• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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
0
mcunn
Asked:
mcunn
2 Solutions
 
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
 
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Anthony PerkinsCommented:
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
 
johanntagleCommented:
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now