How to use a duplicate search in a SELECT request in SQL-CodePudding

I found several duplicate line with this request :

SELECT UID1, UID2, COUNT(*)
FROM TableGroup
GROUP BY UID1, UID2
HAVING COUNT(*) > 1;

Like this :

UID1    UID2
2353    2585    2
2354    2585    2
2355    2585    2
2357    2583    2

UID1 is related with an other table I called NameUID1

2353 Name1
2354 Name2
2355 Name3
2357 Name4

UID2 is related with an other table I called NameUID2

2585 OtherName1
2583 OtherName2

I don't know how to merge the first request for using it in a new request for have the name associated with the UNID1 et UNID2 Like this :

UID1    UID2
Name1   OtherName1
Name2   OtherName1
Name3   OtherName1
Name4   OtherName2

CodePudding user response：

You probably want WITH and joins

WITH cte AS (
    SELECT UID1, UID2, COUNT(*)
    FROM TableGroup
    GROUP BY UID1, UID2
    HAVING COUNT(*) > 1
)
SELECT u1.name, u2.name FROM cte
INNER JOIN NameUID1 u1 ON u1.UID1=u2.UID2
INNER JOIN NameUID2 u2 ON u2.UID2=u2.UID2

CodePudding user response：

Use the select clause as rdge Table and join them all

SELECT t1.Mames,t3.Names
FROM NameUID1 t1
INNER JOIN (    SELECT UID1, UID2
    FROM TableGroup
    GROUP BY UID1, UID2
    HAVING COUNT(*) > 1) t2 ON t1.UID1  = t2.UID1 
INNER JOIN 
NameUID2 t3 ON t2.UID2 = t3.UID2