CSV Query Question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

CSV Query Question

Author

Topic

jeremyC
Starting Member

4 Posts

Posted - 2007-04-25 : 10:24:00

I have a what appears to me to be a complex query question and I was hoping you guys could help me figure out what to do or where I went wrong during setup.

I am basically trying to find a way where I can match multiple rows in one table to multiple rows in another.

To illustrate:

Table A
ListingID | OptionID (Fields)
1 | 3
1 | 9
2 | 3
3 | 9

Table B
OptionID (Fields)
3
9

The idea here is that I want a query that will produce all of the ListingIDs whose OptionIDs match ALL of the rows in Table B. Table B is dynamically generated from a CSV so it changes. Table A is a junction table to the listing table to store the many-to-many relationship between the Options (Table B) and the Listings.

So, the response from the query I am looking for should produce 1 since only ListingID 1 has an OptionID 3 & 9.

I have tried something similar to:

SELECT ListingID FROM table_a
WHERE OptionID IN table_b

Which naturally produces ALL of the items that match either 3 or 9. I simply want to find a way to change that to only produce ListingIDs that match EVERY OptionID in table_b.

If you need more information, let me know. Any advice is greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 10:27:30

If i understand you correctly, you need a simple INNER JOIN ?


select a.ListingID, a.OptionID
from   tablea a inner join table b
on     a.OptionID = b.OptionID

jeremyC
Starting Member

4 Posts

Posted - 2007-04-25 : 10:32:42

That produces all of the items that match ANY of the fields in table b.

Result Set:
ListingID | OptionID
1 | 3
1 | 9
2 | 3
3 | 3

Whereas I am trying to get a result set that has only the ListingIDs whose OptionIDs have EVERY OptionID from Table b.

Thanks for the reply.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-25 : 10:35:51

How about this:

select a.ListingID
from tablea a inner join tableb b
on a.OptionID = b.OptionID
group by a.ListingID
having COUNT(*) = (SELECT COUNT(*) FROM tableb)

SQL Server Helper
http://www.sql-server-helper.com

jeremyC
Starting Member

4 Posts

Posted - 2007-04-25 : 10:40:27

That's brilliant. So basically I receive the result set in my second post, and then I return the result set who's # of IDs match the # of IDs in table b.

Thanks a ton sshelper. You have no idea how long I have been searching how to do this.

:)

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-25 : 10:43:05

Just a warning, this query might give you a wrong result if there are duplicate records in your first table. As an example, the following sample data will give you the same result:

Table A
ListingID | OptionID (Fields)
1 | 3
1 | 3
2 | 3
3 | 9

If your data won't have duplicate values, then the query should work.

SQL Server Helper
http://www.sql-server-helper.com

jeremyC
Starting Member

4 Posts

Posted - 2007-04-25 : 10:58:07

The first table is a junction table with composite primary keys. That being said, there will never be a possibility for it to have duplicate records. Thanks for the head's up though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources