don't find the solution... - 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

General SQL Server Forums

New to SQL Server Programming

don't find the solution...

Author

Topic

oliver2202
Starting Member

2 Posts

Posted - 2009-05-23 : 08:44:50

Hi everybody

So I 'have 3 tables:

--------------------
catego

id name
1 Home
2 Entertainment
3 Pictures
4 Junk

--------------------
products
id nom
1 ABC123
2 DEF456
3 GHI789
4 JKL123
5 MNO456
6 PQR789
---------------
poduct_catego

product_id category_id
1 1
1 2
1 3
2 2
2 3
3 2
4 1
5 1
6 2
6 3
----------------------------

I can't write an SQL Statement that lists all records in the "products" table which have two or more related categories.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-23 : 09:12:53

[code]select p.id, p.nom
from product p
inner join poduct_catego c on p.id = c.product_id
group by p.id, p.nom
having count(*) > 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

oliver2202
Starting Member

2 Posts

Posted - 2009-05-23 : 09:27:36

thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 11:07:59

[code]
SELECT p.*
FROM products p
INNER JOIN (SELECT product_id
FROM product_catego
GROUP BY product_id
HAVING COUNT(DISTINCT category_id)>1) pc
ON p.product_id=pc.product_id
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources