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.
LVL 37
Neil RussellTechnical Development LeadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Reformatting your first SQL statement...
SELECT DISTINCT p.* 
FROM PARTS p 
   JOIN PartsCategories pc ON p.id = pc.FKParts
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
Does this mean that there can be multiple parts rows for a single PartsCategory, and you want only the part numbers where there are at least three PARTS table rows, with categories 12, 22, and 33?
Neil RussellTechnical Development LeadAuthor Commented:
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?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Neil RussellTechnical Development LeadAuthor Commented:
@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
Scott PletcherSenior DBACommented:
SELECT  * from PARTS where Id in
(
SELECT FKPARTS
FROM PartsCategories
WHERE FKCategory in (12,22,33)
GROUP BY FKPARTS
HAVING COUNT(DISTINCT FKCategory) = 3
)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
First attempt:

let A be the PART and B the category:  (using the category table as temp)
This assumes that a part can't be in the same category more than once.
select 1 a, 1 b into #temp
insert into #temp select 1, 2
insert into #temp select 1, 3
insert into #temp select 2, 2
insert into #temp select 2, 3
insert into #temp select 3, 3
insert into #temp select 3, 4

select distinct t.* from 
#temp t
join (
select a from #temp 
 where b in (2,3)
  group by a
  -- this would be the number of items in your in.
 having COUNT(*) >= 2
) x on t.a = x.a
order by t.a

Open in new window

Kyle AbrahamsSenior .Net DeveloperCommented:
Scott's and I are pretty close . . . you can use his but

HAVING COUNT(DISTINCT FKCategory) = 3

should be
 
HAVING COUNT(DISTINCT FKCategory) >= 3
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Scott is correct, the subselect only asks for 3 categories, so the distinct count can only be <=3 ;-)
Kyle AbrahamsSenior .Net DeveloperCommented:
Yeah . . . brain is fried . . . oversight on my part.
Neil RussellTechnical Development LeadAuthor Commented:
Yes Scotts works perfectly with =3
Bang on and simple :D
Neil RussellTechnical Development LeadAuthor Commented:
Simple and easy to understand! As are all things once you know the answer!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.