SQL Query using OR now want AND alternative.

Neil Russell
Neil Russell used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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 Lead

Author

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 Advisor
Top Expert 2015

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Neil RussellTechnical Development Lead

Author

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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT  * from PARTS where Id in
(
SELECT FKPARTS
FROM PartsCategories
WHERE FKCategory in (12,22,33)
GROUP BY FKPARTS
HAVING COUNT(DISTINCT FKCategory) = 3
)
Kyle AbrahamsSenior .Net Developer
Commented:
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 Developer

Commented:
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 Advisor
Top Expert 2015

Commented:
Scott is correct, the subselect only asks for 3 categories, so the distinct count can only be <=3 ;-)
Kyle AbrahamsSenior .Net Developer

Commented:
Yeah . . . brain is fried . . . oversight on my part.
Neil RussellTechnical Development Lead

Author

Commented:
Yes Scotts works perfectly with =3
Bang on and simple :D
Neil RussellTechnical Development Lead

Author

Commented:
Simple and easy to understand! As are all things once you know the answer!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial