Matching data of 2 tables - 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 2000 Forums

SQL Server Development (2000)

Matching data of 2 tables

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-23 : 10:35:54

Hi
I need some help in writing the following query.
I have 2 tables:


Product:
Name    ID
Prod1        1
prod1        2
prod2        1


TempTable:
ID
1
2

Now I want to write a query that returns all the Products that have ALL the matching ids from TempTable (the IDs in TempTable can be more then 2).

So for the above tables, I should ONLY get prod1. This is because prod2 does not contain ID=2

Could you help in writing this query. Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-23 : 11:39:44

I don't see the logic ehind this as neither prod1 or prod2 have an ID of 3 and therefore from what you said you wouldn't want to return anything?!?

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-23 : 12:00:43

RickD, thanks for pointing out this bug in my post, i have edited my post (deleted 3 from the TempTable).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 02:42:30

Try this

Select Name from Product
where id in (select id from temptable)
group by Name having count(name)=(Select count(*) from temptable)

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-24 : 05:00:23

Thanks Madhivanan.

Could you explain this statement:
group by Name having count(name)=(Select count(*) from temptable)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 05:28:15

>>all the Products that have ALL the matching ids from TempTable

So the count of Name should equal count in temptable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources