Hi new to this site, hoping someone might be able to help me with this SQL question.
So I have a table called T_EE_EVALUATIONS that has duplicate records within in it. The table has the following structure below. I need to find the duplicate address where the house_id is not the same as it's duplicate address. Neither ID's are P keys. I tried this but it doesn't really work the way I want it to work.
Select E.eval_id , E.house_id , E.ClientCity, E.ClientAddr, E.Houseregion, T.eval_id , T.house_id, T.ClientCity , T.ClientAddr , T.Houseregion
From T_EE_EVALUATIONS E INNER JOIN
(Select eval_id , house_id,ClientCity, ClientAddr, Houseregion, COUNT(*) AS CountOf
FROM T_EE_EVALUATIONS GROUP BY eval_id , house_id,ClientCity, ClientAddr, Houseregion
) T
ON E.ClientCity || E.ClientAddr || E.Houseregion = T.ClientCity || T.ClientAddr || T.Houseregion
eval_id house_id ClientCity ClientAddr Houseregion
------------------------------------------------------------
12345 56455 Quebec 34 Anyplace Quebec
12345 23765 Quebec 34 Anyplace Quebec
12345 94213 Quebec 34 Anyplace Quebec
13456 23456 Russell 214 mystreet Ontario
13456 33456 Russell 214 mystreet Ontario
14526 14567 Quispamsis 456 Thatstreet New Brunswick
14526 13567 Quispamsis 456 Thatstreet New Brunswick
What I have tried:
Select E.eval_id , E.house_id , E.ClientCity, E.ClientAddr, E.Houseregion, T.eval_id , T.house_id, T.ClientCity , T.ClientAddr , T.Houseregion
From T_EE_EVALUATIONS E INNER JOIN
(Select eval_id , house_id,ClientCity, ClientAddr, Houseregion, COUNT(*) AS CountOf
FROM T_EE_EVALUATIONS GROUP BY eval_id , house_id,ClientCity, ClientAddr, Houseregion
) T
ON E.ClientCity || E.ClientAddr || E.Houseregion = T.ClientCity || T.ClientAddr || T.Houseregion