How to apply an array of AND conditions

Hi Experts,

Forgive me if this is a basic question but I haven't been able to find a resource online that answers this.

I'm trying to apply an array of conditions to a SQL SP.  To do so I want to take a lookup table

RecipeID, CategoryID
1,1
1,2
2,1
2,2
2,3
2,5
3,4
4,1
4,2
4,3
4,4

and return the RecipeID's for all records that match ALL of the variables in a table variable

CategoryID
1
2
3

With results like:

Result
2
4


Any Hints?

Thanks!

Michael
mcunnAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
This is how I tested it:
DECLARE @Lookup TABLE (
	RecipeID integer, 
	CategoryID integer)

DECLARE @Categories TABLE (
	CategoryID integer)

SET NOCOUNT ON

INSERT	@Lookup(RecipeID, CategoryID)
VALUES	(1,1),
	(1,2),
	(2,1),
	(2,2),
	(2,3),
	(2,5),
	(3,4),
	(4,1),
	(4,2),
	(4,3),
	(4,4)

INSERT	@Categories(CategoryID)
VALUES	(1), (2), (3)

SELECT	RecipeID
FROM	@Lookup l
	INNER JOIN @Categories c ON l.CategoryID = c.CategoryID
GROUP BY
	RecipeID
HAVING	COUNT(*) = 3

Output:
RecipeID
2
4

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
select distinct recipeid
from table where categoryid in (select categoryID from table array)
0
 
mcunnAuthor Commented:
Correct me if I'm wrong but wouldn't this pull records that match any of the criteria?

Results
1
2
3
4

I need it to match all criteria.

Results
2
4


Thanks



0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Anthony PerkinsCommented:
Something like this perhaps:
SELECT	RecipeID
FROM	Lookup l
	INNER JOIN Categories c ON l.CategoryID = c.CategoryID
GROUP BY
	RecipeID
HAVING	COUNT(*) = 3

Open in new window

0
 
johanntagleConnect With a Mentor Commented:
Yes acperkin's solution should do it.  Just want to do a simple enhancement:

SELECT	RecipeID
FROM	Lookup l
	INNER JOIN Categories c ON l.CategoryID = c.CategoryID
GROUP BY
	RecipeID
HAVING	COUNT(*) = (select count(CategoryID) from Categories)

Open in new window


This way you don't have to modify the query whenever the number of rows in Categories changes.  The above also assumes CategoryID is unique in Categories and the combination of RecipeID and CategoryID is unique in Lookup.  Otherwise you need to tweak it a little:

SELECT	RecipeID
FROM	Lookup l
	INNER JOIN Categories c ON l.CategoryID = c.CategoryID
GROUP BY
	RecipeID
HAVING COUNT(distinct CategoryID) = (select count(distinct CategoryID) from Categories)

Open in new window

0
 
mcunnAuthor Commented:
Thanks Guys.

Not the most efficient query but it works perfectly fine for dataset I'm working with.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.