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.
| Author |
Topic |
|
thom77
Starting Member
2 Posts |
 Posted - 2007-08-22 : 20:12:06
|
Hi Guys,I have a question that I'm hoping someone can answer. Its actually a bit more complex than this, but I think if someone can tell me how to do this, I can apply that logic to the larger query.Lets say I have 3 tables:ProductsCategoriesProducts_CategoriesSo basically I am able to assign one product to multiple categories.I am able to query this for ONE category very easily. For example: quote:
Select * FROM products inner join products_categories on products.pID=products_categories.pID where cID=2
My problem is that I cannot figure out how to return one product with multiple categories. eg the following query will return no results:quote:
Select * FROM products inner join products_categories on products.pID=products_categories.pID where cID in (1,2)
It actually gets much more complex than this because there are other types of categories where i need to query multiple values against, but I think that if I am able to understand how to do this in its simplest form, I can figure out the rest. Thank you so much in advance!!!  |
|
|
thom77
Starting Member
2 Posts |
Posted - 2007-08-22 : 21:46:52
|
| hi everyone. a friend of mine helped me out. i figured i would post the solution here in case anyone else has a similar situation.select * FROM products where pid in (select c1.pid from (select distinct pid from Products_Categories where cid = 1) c1 inner join(select distinct pid from Products_Categories where cid = 2) c2 on c1.pid=c2.pid) |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 03:40:01
|
SELECT pid FROM Products_Categories group by pidhaving min(cID) <> max(cID)SELECT pid FROM Products_Categories group by pidhaving count(distinct cID) > 1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
Complex Query Question