Link to home
Create AccountLog in
Avatar of Neil Russell
Neil RussellFlag for United Kingdom of Great Britain and Northern Ireland

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.

SELECT  DISTINCT * from PARTS where Id in (Select FKPARTS from PartsCategories WHERE FKCategory in (12,22,33))

Open in new window


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.
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Neil Russell

ASKER

The relationship is that Each PART can have any number of Categories. The join table is PartsCategories
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?
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?
@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
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Scott's and I are pretty close . . . you can use his but

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.
Yes Scotts works perfectly with =3
Bang on and simple :D
Simple and easy to understand! As are all things once you know the answer!