Find records with exact matches in many ways

Question

I have three tables that look like this:

PROD

Prod_ID|Desc ------------ P1|Foo1 P2|Foo2 P3|Foo3 P4|Foo4 ... 

RAM

 Ram_ID|Desc ------------ R1|Bar1 R2|Bar2 R3|Bar3 R4|Bar4 ... 

PROD_RAM

 Prod_ID|Ram_ID ------------ P1|R1 P2|R2 P3|R1 P3|R2 P3|R3 P4|R3 P5|R1 P5|R2 ... 

Between PROD and RAM there is a Many-To-Many relationship described by the PROD_RAM table .

Given Ram_ID set as (R1,R3) , I would like to find all PROD that have ONLY ONE or ALL RAM this set.

In this case (R1,R3) should be returned, for example, P1 , P4 and P5 ; P3 should not be returned because it has R1 and R3 , but also R2 .

What is the fastest request to get the entire PROD that has exactly one or one of the Ram_ID given RAM set?

EDIT:
The PROD_RAM table may contain a ratio greater than 1-> 3, so hard-coded checks for count = 1 OR = 2 are not a viable solution.

Answer 1

Another solution you could try for speed would be this:

 ;WITH CANDIDATES AS ( SELECT pr1.Prod_ID , pr2.Ram_ID FROM PROD_RAM pr1 INNER JOIN PROD_RAM pr2 ON pr2.Prod_ID = pr1.Prod_ID WHERE pr1.Ram_ID IN ('R1', 'R3') ) SELECT * FROM CANDIDATES WHERE CANDIDATES.Prod_ID NOT IN ( SELECT Prod_ID FROM CANDIDATES WHERE Ram_ID NOT IN ('R1', 'R3') ) 

or if you do not like to repeat the terms and conditions

 ;WITH SUBSET (Ram_ID) AS ( SELECT 'R1' UNION ALL SELECT 'R3' ) , CANDIDATES AS ( SELECT pr1.Prod_ID , pr2.Ram_ID FROM PROD_RAM pr1 INNER JOIN PROD_RAM pr2 ON pr2.Prod_ID = pr1.Prod_ID INNER JOIN SUBSET s ON s.Ram_ID = pr1.Ram_ID ) , EXCLUDES AS ( SELECT Prod_ID FROM CANDIDATES LEFT OUTER JOIN SUBSET s ON s.Ram_ID = CANDIDATES.Ram_ID WHERE s.Ram_ID IS NULL ) SELECT * FROM CANDIDATES LEFT OUTER JOIN EXCLUDES ON EXCLUDES.Prod_ID = CANDIDATES.Prod_ID WHERE EXCLUDES.Prod_ID IS NULL 

Answer 2

 SELECT Prod_ID FROM ( SELECT Prod_ID , COUNT(*) AS cntAll , COUNT( CASE WHEN Ram_ID IN (1,3) THEN 1 ELSE NULL END ) AS cntGood FROM PROD_RAM GROUP BY Prod_ID ) AS grp WHERE cntAll = cntGood AND ( cntGood = 1 OR cntGood = 2 --- number of items in list (1,3) ) 

I’m not at all sure that this is the fastest way. You will have to try different ways of writing this query (using JOIN and NOT EXISTS ) and check the speed.

Answer 3

One way to do this would be as follows:

 SELECT PROD.Prod_ID FROM PROD WHERE (SELECT COUNT(*) FROM PROD_RAM WHERE PROD_RAM.Prod_ID = PROD.Prod_ID) > 0 AND (SELECT COUNT(*) FROM PROD_RAM WHERE PROD_RAM.Prod_ID = PROD.Prod_ID AND PROD.Ram_ID <> IFNULL((SELECT TOP 1 Ram_ID FROM PROD_RAM WHERE PROD_RAM.Prod_ID = PROD.Prod_ID),0)) = 0