my SQL doesn't handle REPEATS as I'd like 1

[VickyC]

hi to all

I have SQL that counts, for each of a large number of trials, matches between columns X and Y of tbl_XY.

 [b]
qry_XY [/b]

SELECT T1.Trial, COUNT(*) AS MatchCount
FROM tbl_XY AS T1 INNER JOIN tbl_XY AS T2 ON (T1.Trial=T2.Trial) AND (T1.X=T2.Y)
GROUP BY T1.Trial;

In the table shown below, when TRIAL = 1, the SQL output correctly shows that there are 2 matches (X=Y=23 and X=Y=31).

The SQL works perfectly for the vast majority of my Trials, but it fails when there are REPEATS in either the X or the Y column. For example, in Trial 276 the SQL indicates that there are 2 matches (X=Y=44 and X=Y=44). But, I want there to be only 1 match indicated here. After the 1st X=44 matches Y=44, these values need to be removed from further consideration. The 2nd X=44 should find no match in the Y column.

Using the same reasoning, I want Trial 3544 to indicate 5 matches, not 12.

 [b]
tbl_XY                                   qry_XY (ACTUAL output)               qry_XY  (DESIRED output)
Trial	Part	X	Y              Trial    MatchCount                  Trial    MatchCountbb[/b]
1	1	23	57                 1        2                           1        2
1	2	99	6                276        2                         276        1  
1	3	18	47              3544       12                        3544        5
1	4	31	8
1	5	88	31
1	6	66	23

276	1	44	16
276	2	44	97
276	3	35	3
276	4	7	88
276	5	46	44
276	6	79	19

3544	1	41	24
3544	2	13	13
3544	3	41	24
3544	4	24	41
3544	5	24	41
3544	6	13	41

I'd be grateful for any hints pointing me in the right direction. Vicky C.

 

[PHV]

What about this ?

SELECT T1.Trial, COUNT(*) AS MatchCount
FROM (SELECT DISTINCT Trial, X FROM tbl_XY) AS T1
INNER JOIN (SELECT DISTINCT Trial, Y FROM tbl_XY) AS T2
ON T1.Trial=T2.Trial AND T1.X=T2.Y
GROUP BY T1.Trial

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

hi PHV and all. That gets closer to a solution, but it still has a glitch.

In Trial 3544, the matches are...

the 1st X=41 partners with the 1st Y=41
the 2nd X=41 partners with the 2nd Y=41 (the 3rd Y=41 is left unmatched)

the 1st X=13 partners with the 1st Y=13 (the 2nd X=13 is left unmatched)

the 1st X=24 partners with the 1st Y=24
the 2nd X=24 partners with the 2nd Y=24

So, Trial 3544 has 5 pairings in all. The DISTINCT Subqueries work if a number appears once as X and multiple times as Y (or vice versa), but it fails when a number appears multiple times in both the X and Y columns.

Thanks again for help provided already, and for any further thoughts. Vicky C.

 

[PHV]

So, try this:

SELECT T1.Trial,Sum(IIf(CountX<CountY,CountX,CountY)) AS MatchCount
FROM (SELECT Trial,X,Count(*) AS CountX FROM tbl_XY GROUP BY Trial,X) T1
INNER JOIN (SELECT Trial,Y,Count(*) AS CountY FROM tbl_XY GROUP BY Trial,Y) T2
ON T1.Trial=T2.Trial AND T1.X=T2.Y
GROUP BY T1.Trial

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks for an excellent solution - not sure I would have ever got that one on my own!

By the way, I'd already realized that any solution to my question that used an INNER JOIN, as your solution did, would cause grief if there were NO MATCHES, because the joined tables would have no records. So, I use the following to deal with the special case where MatchCount = 0.

SELECT T.Trial, 0 AS MatchCount
FROM tbl_XY AS T
GROUP BY T.Trial
HAVING Max(DCount('*',"tbl_XY","Trial=" & T.Trial & " AND Y=" & X)=0);

Thanks again for your help with this problem Vicky C.

 

[PHV]

Why another query ?

SELECT T1.Trial,Sum(Nz(IIf(CountX<CountY,CountX,CountY),0)) AS MatchCount
FROM (SELECT Trial,X,Count(*) AS CountX FROM tbl_XY GROUP BY Trial,X) T1
LEFT JOIN (SELECT Trial,Y,Count(*) AS CountY FROM tbl_XY GROUP BY Trial,Y) T2
ON T1.Trial=T2.Trial AND T1.X=T2.Y
GROUP BY T1.Trial

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Ah, of course. That's even better. Thanks again! Vicky C,