asked on SQL Query using OR now want AND alternative.
I have the following SQL query that works just fine where I want all PARTS that have ANY of the selected Categories.
What I want now is the equivalent to select ONLY those PARTS where they have a Category of 12 AND 22 AND 33
Most compact and easiest bearing in mind that the select statement is built up dynamically based on a selection of Categories in a front end app with tick boxes.
SELECT DISTINCT * from PARTS where Id in (Select FKPARTS from PartsCategories WHERE FKCategory in (12,22,33))
What I want now is the equivalent to select ONLY those PARTS where they have a Category of 12 AND 22 AND 33
Most compact and easiest bearing in mind that the select statement is built up dynamically based on a selection of Categories in a front end app with tick boxes.
Microsoft SQL ServerMicrosoft SQL Server 2008Databases
Last Comment
Neil Russell
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Do you mean "exactly all of those categories" or "at least all of those categories"? So there may be more, but not less, then the given categories?
ASKER
@Qlemo
Correct, there must be ALL of the ones in question but may be others.
So if a Part had categories of 1,5,7,12,22 and 33 it would still match 12,22,33 but NOT match 7,12,23
Correct, there must be ALL of the ones in question but may be others.
So if a Part had categories of 1,5,7,12,22 and 33 it would still match 12,22,33 but NOT match 7,12,23
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Scott's and I are pretty close . . . you can use his but
HAVING COUNT(DISTINCT FKCategory) = 3
should be
HAVING COUNT(DISTINCT FKCategory) >= 3
HAVING COUNT(DISTINCT FKCategory) = 3
should be
HAVING COUNT(DISTINCT FKCategory) >= 3
Scott is correct, the subselect only asks for 3 categories, so the distinct count can only be <=3 ;-)
Yeah . . . brain is fried . . . oversight on my part.
ASKER
Yes Scotts works perfectly with =3
Bang on and simple :D
Bang on and simple :D
ASKER
Simple and easy to understand! As are all things once you know the answer!
that has an Id, FKCategory and FKParts, respectively the Key of a category and a part.
So if I say I want Parts for Categories 12,22 and 33 then yes, all 3 rows must exist in PartsCategories for a Part to be included in the result set.
If i had Categories of 12 and 22 then of course all Parts with those two categories would be included.
Oh and of course the Categories and not Exclusive, a part can have other categories but those are not part of the select.
Hope thats clear?