compare results of 2 subqueries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

compare results of 2 subqueries

Author

Topic

samham
Starting Member

12 Posts

Posted - 2003-01-14 : 12:37:29

i want to compare the results of 2 subqueries... something similar to this:
subquery1 IN subquery2

i cant use the IN unless subquery1 returns a single value i want to compare multiple results like for example:
('name1','name2') in ('name1','name2','name3') returns true

how can i acheive this?

thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-14 : 12:44:33

You haven't given much detail (like ddl, sample data or expected resutls), but I'm guessing your solution is going to involve an INNER JOIN or a WHERE NOT EXISTS (<correlated subquery>) ...

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-14 : 12:59:56

One way to do it is to join one subquery to the other:

SELECT
A.Field1, A.Field2,
FROM
(query 1) A
LEFT OUTER JOIN
(query 2) B
ON A.Field1 = B.Field1 and A.Field2 = B.Field2

If any of B's key fields are Null, there is a record in A and not in B.

- Jeff

samham
Starting Member

12 Posts

Posted - 2003-01-14 : 14:33:07

thanks for the replies

i thihk i didnt explain well what i wanted.
what i have is a set of words, comma seperated (they r keywords for search).
i have between many tables the following 3:
"tbl_text"
"tbl_Keyword"
"tbl_textKeyword" (which relates both tables with a many to many relationship)
first i search for texts based on many parameters and fields and when it comes to limit the search based on keywords,i get from my application the comma seperated list of keywords to search and i want to get from the db the texts where all the keywords match, so i thought of checking if the list i have(from the app) is included in the list of all the keywords of the text.
Is this the correct way or should i use another alternative like checking one word at a time if it exists in the list of the text keywords.

Hope this is better explained and thanks again.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-14 : 14:54:43

Or you could use something like Rob Volk's article Parsing CSV Values into Multiple Rows to create two temporary tables that could then be joined together using the above posts.

------------------------------------------------------
The more you know, the more you know you don't know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources