Hi Tom (+ group), and thanks for gettng back to me,
> Maybe the GROUP BY
> clause should just be "GROUP BY t1key, t2key"?
No "maybe" about it Tom - I continued working on it and the query I
finally came up with (which now works perfectly for both PG and MySQL)
SELECT
DISTINCT LEAST(t1key, t2key) AS "lst",
GREATEST(t1key, t2key) AS "gst",
COUNT(LEAST(t1key, t2key)) AS "mn_c" -- << NOT NECESSARY - SHOWS NO. OF DUPS
-- COUNT(GREATEST(t1key, t2key)) AS mx_c
FROM
(
SELECT t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value",
t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value"
FROM tab t1
JOIN tab t2
ON t1.t_key != t2.t_key
AND t1.t_name = t2.t_name
AND t1.t_value = t2.t_value
ORDER BY t1.t_id, t2.t_id
)
AS t1
GROUP BY t1.t1key, t1.t2key --- <<<< Exactly as you suggested
HAVING
COUNT(LEAST(t1key, t2key)) -- <<<<
= (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1.t1key)
AND
COUNT(GREATEST(t1key, t2key))
= (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1.t2key)
ORDER BY 1, 2;
And now the two "reciprocal" HAVING clauses pick out the required
records perfectly. I can sort of see the PG philiosphy of being
stricter - the ONLY_FULL_GROUP_BY fiasco springs to mind.
The PG fiddle
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e48caa900335a27e390a5394f4faef28
and MySQL one
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1b89903cb96d44c145b48a8e5172f289
It shows exactly the result desired - sets of records grouped by t_key
which are identical in both t_name and t_value are chosen
gst mn_c
75 76 4
75 78 4
76 78 4
85 86 3
92 93 2
94 95 1
So 75 is identical to 76 and 78. 85 is identical to 86 and so on.
The beauty of having chosen to test with PostgreSQL is that if I
hadn't done it, my original semi-working MySQL solution could have
failed under production conditions (sorry not could, would have
failed). Again proving the (virtually) infinite superiority of
PostgreSQL over MySQL.
Thanks again and rgs,
Pól...
> regards, tom lane
