How to find duplicate rows from three tables using two columns-CodePudding

—oracle sql-

Select *
  From Client cl, Contact c, Location l
 Where l.locID     = c.locID 
   and cl.clientID = l.clientID

I want to find more than one occurrences(duplicates) using combined columns of locID and clientID

CodePudding user response：

We can use aggregation here:

SELECT cl.clientID, l.locID
FROM Client cl
INNER JOIN Location l ON l.clientID = cl.clientID
INNER JOIN Contact c ON c.locID = l.locID
GROUP BY cl.clientID, l.locID
HAVING COUNT(*) > 1;

Note that I also refactored your query to use explicit joins.

CodePudding user response：

You can get all the details of the rows using the analytic COUNT function:

SELECT *
FROM   (
  SELECT cl.clientID,
         cl.otherClientColumn,
         c.locID,
         c.otherContactColumn,
         l.otherLocationColumn,
         COUNT(*) OVER (PARTITION BY cl.clientID, c.locID) AS num_duplicates
  From   Client cl
         INNER JOIN Contact c
         ON (cl.clientID = l.clientID)
         INNER JOIN Location l
         ON (l.locID     = c.locID)
)
WHERE  num_duplicates > 1