how to write the query with sub sub query

Question

Post reply

how to write the query with sub sub query

rajeshhalyal

SSC Rookie

Points: 49
More actions
September 17, 2009 at 11:15 am

#74100

I have a field in table which contains comma delimited id values for each related record.
Now I want to find the records from main table with Ids not in this fields from related table.
all the ids are numeric in the main table and the related table has fields "splink" which contains comma delimited values of main table.
simple select with like
select * from maintable where id not in (select linkid from relatedtable )
does not work here
How do I go about it.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

you might want to search for "split string function" on this site. There are many solutions available.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

rajeshhalyal SSC Rookie Points: 49 More actions · Answer 2

It would be very helpful if you point me to the exact link.

Rajesh

LutzM SSC Guru Points: 107049 More actions · Answer 3

This link should help you further.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Supriya.S Ten Centuries Points: 1131 More actions · Answer 4

LOL :laugh:

Really funny Lutz. I had not seen that site before. Guess Rajesh asked for it..:-)

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 5

I have to say I am truly impressed Lutz, good job, I need to be able to do that.

Cheers,

J-F

rajeshhalyal SSC Rookie Points: 49 More actions · Answer 6

I have solved the problem by creating a new table which contains the id of related table and record for each id of main table. updated it in the trigger.

But this was possible because I had access to modify the tables.

LutzM SSC Guru Points: 107049 More actions · Answer 7

Once it a while it's required to point out that we're all volunteers who are willing to help. The link above is just one possible way to transfer the message. 😀

We're no online-{F1}-replacement nor charity consultants.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

rajeshhalyal SSC Rookie Points: 49 More actions · Answer 8

I have been playing with split function but was not able to use it for fields directly from select command.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

lmu92 (9/17/2009)
This link should help you further.

Heh... not so funny, Lutz 😉 ... the very first article that shows up contains both a UDF and a While Loop. At least point him to an article that isn't going to kill him performance wise. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

rajeshhalyal (9/17/2009)
I have a field in table which contains comma delimited id values for each related record.
Now I want to find the records from main table with Ids not in this fields from related table.
all the ids are numeric in the main table and the related table has fields "splink" which contains comma delimited values of main table.
simple select with like
select * from maintable where id not in (select linkid from relatedtable )
does not work here
How do I go about it.

Please see the following article. It shows not only how to avoid certain While Loops, but it also shows how to do splits without them. There are several other methods (only one or two are a bit faster) but this should suit you just fine.

http://www.sqlservercentral.com/articles/T-SQL/62867/

There's also a useful follow up to the article here...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)