Identifying matching sets of values in 2 tables 1

[teach314]

hello - I am asking for some hints about how to MATCH sets of values in tbl_A with sets of values in a master table. (a sample of each table is shown below).

Each Set has exactly 3 distinct Values. The Rank columns identify the Values in ASC order.

[bold]
tbl_A                                            tbl_Master
ID_A    Set_A    Value_A    Rank_A               ID_M    Set_M    Value_M    Rank_M [/bold]
                                                 ......
 1        9         36        2                  2401      81        16         1
 2        9         44        3                  2402      81        38         2    
 3        9         13        1                  2403      81        77         3 

 4       15         99        3                  2404      82        19         1
 5       15         62        2                  2405      82        36         2
 6       15          4        1                  2406      82        64         3 

 7       20         19        1                  2407      83        13         1
 8       20         64        3                  2408      83        36         2
 9       20         36        2                  2409      83        44         3

10   etc......                                   2410   etc......

I'm trying to get output that MATCHES Sets in tbl_A with Sets in tbl_Master. Like this...

[bold]
Set_A   Set_M [/bold]
  9       83 
 15
 20       82

Thanks in advance for any advice.
Teach314

 

[MajP]

SELECT 
 TblA1.Set_A, 
 tblMaster.Set_M, 
 Count(tblMaster.Value_M) AS ItemsInM
FROM 
 TblA1 
INNER JOIN 
 tblMaster ON TblA1.Value_A = tblMaster.Value_M
GROUP BY 
 TblA1.Set_A, 
 tblMaster.Set_M
HAVING 
 Count(tblMaster.Value_M) = (SELECT Count(A.Value_A) AS ItemsInA FROM TblA1 AS A Where A.Set_A = TblA1.Set_A)

Should work if there is not duplicated values in a set

 

[teach314]

Hey MajP - that works GREAT!
It was your HAVING clause that I would have never got on my own. It was also neat that you did not need the Rank columns. (these had been added to help solve the current problem that you solved without them.)

Is there an easy way to show that tbl_A/Set_A = 15 has NO match in tbl_Master/Set_M? I tried changing your INNER JOIN to a LEFT JOIN, but I get the same output.

Much thanks for your nice solution.
Teach314

 

[MajP]

This solution will works if the amount of matches equals the amount of items in set A. But a couple things would fail. Your rules may or may not allow these conditions.

1) M has more items than A
assume A has 11, 15, 19
and master has 11, 15, 19, 31
then everything in A matches with M but the set in m has more items than A. This will be returned as a match.

This could be fixed by adding another condition to check that the amount of Items in set M equal the amount of items in set A. It currently only checks the other direction.

2) If A has 11,12,15
and the Master has 11,11,11 this will be returned as a match because there are three matches. Same if
A has 11,11,15 and M has 11,12,15

 

[MajP]

SELECT 
 TblA1.Set_A, 
 tblMaster.Set_M, 
 Count(tblMaster.Value_M) AS ItemsInM, 
 Count(TblA1.value_A) AS ItemsInA
FROM 
 TblA1 
INNER JOIN 
 tblMaster ON TblA1.Value_A = tblMaster.Value_M
GROUP BY 
 TblA1.Set_A, 
 tblMaster.Set_M
HAVING 
 Count(tblMaster.Value_M) = (SELECT Count(A.Value_A) AS ItemsInA FROM TblA1 AS A Where A.Set_A = TblA1.Set_A)
AND
  Count(tblMaster.Value_M) = (SELECT Count(M.Value_M) AS ItemsInM FROM TblMaster AS M Where M.Set_M = TblMaster.Set_M)

I think this is closer because the amount of returned records per set has to equal the amount of records available records in set A and also the amount of available records in set B.
This would solve most things except cases like the following.
11,11,15 And 15,15,11

 

[teach314]

Thanks again MajP for some good insights. Here's a few comments...

a) In the 'real' tables, each Set in both tables all have exactly 10 values, so your first point doesn't become an problem.

b) Your 2nd point raises the issue of repeated Values. When I originally posed the problem, I stated that "each Set contains 3 DISTINCT values". It has since dawned on me that I could use your SQL as a very powerful tool in another setting if it could handle duplicates as well(where, say, 11,11,15 matches ONLY 11,11,15, NOT 11,12,15.)

Are there any hints about how I could approach this problem?

Thanks
Teach314

 

[teach314]

hello - I kept tweaking MajP's excellent code to get the code shown below. I believe it handles duplicates AND proper order. So, A(11,11,15) MATCHES only M(11,11,15), not M(11,15,11).

SELECT 
		Q.Set_A, Q.Set_M
FROM 	(

		SELECT 
			TA.Set_A, TM.Set_M, TA.Value_A, TM.Value_M, TA.Rank_A, TM.Rank_M
		FROM 
			tbl_A AS TA  INNER JOIN  tbl_Master AS TM   ON   TA.Value_A=TM.Value_M
		WHERE 
			TA.Rank_A=TM.Rank_M
			
		) AS Q
	
GROUP BY 
		Q.Set_A, Q.Set_M
HAVING 
		(Sum(Q.Rank_A)=6) And (Sum(Q.Rank_M)=6);

I had to use two additional ideas. The Rank columns in the original tables really show position. So, A(11,11,15) would have positions 1,2 and 3 respectively, in ASC order. Also, I used 'Sum(Q.Rank_A)=6' and 'Sum(Q.Rank_M)=6' in the HAVING clause, because the only way to 'get a 6' is if the position 1,2 and 3 Values within a given Set match.

I'm sure the gurus out there could write more general code, but I believe this will suit the original purpose.

Teach314

SELECT 
		Q.Set_A, Q.Set_M
FROM 	(

		SELECT 
			TA.Set_A, TM.Set_M, TA.Value_A, TM.Value_M, TA.Rank_A, TM.Rank_M
		FROM 
			tbl_A AS TA  INNER JOIN  tbl_Master AS TM   ON   TA.Value_A=TM.Value_M
		WHERE 
			TA.Rank_A=TM.Rank_M
			
		) AS Q
	
GROUP BY 
		Q.Set_A, Q.Set_M
HAVING 
		(Sum(Q.Rank_A)=6) And (Sum(Q.Rank_M)=6);