I want to remove the duplicate rows Using Select Query how can I get rid of the duplicate rows.

The following out put produces redundant records, How to get the distinct results?

SELECT E.EMAIL_ID, T.FIRST_NAME, T.LAST_NAME, CY.COUNTRY_ID 
FROM PLAYER P
INNER JOIN PLAYERTYPE T ON P.PLAYER_ID = T.PLAYER_ID
INNER JOIN PLAYER_CONTACT C ON T.PLAYER_ID = C.PLAYER_ID 
INNER JOIN CONTACT_EMAIL E ON E.CONTACT_ID = C.CONTACT_ID
INNER JOIN COUNTRY_TABLE CY ON P.COUNTRY_ID = CY.COUNTRY_ID
WHERE CY.COUNTRY_CODE='AUS'
AND T.PLAYER_TYPE IN ('NEW', 'EXE')

Current Output:

Expected Output:

Tried

SELECT DISTINCT E.EMAIL_ID, T.FIRST_NAME, T.LAST_NAME, CY.COUNTRY_ID 
FROM PLAYER P
INNER JOIN PLAYERTYPE T ON P.PLAYER_ID = T.PLAYER_ID
INNER JOIN PLAYER_CONTACT C ON T.PLAYER_ID = C.PLAYER_ID 
INNER JOIN CONTACT_EMAIL E ON E.CONTACT_ID = C.CONTACT_ID
INNER JOIN COUNTRY_TABLE CY ON P.COUNTRY_ID = CY.COUNTRY_ID
WHERE CY.COUNTRY_CODE='AUS'
 AND T.PLAYER_TYPE IN ('NEW', 'EXE')

SELECT T.FIRST_NAME, T.LAST_NAME, E.EMAIL_ID, CY.COUNTRY_ID 
FROM PLAYER P
INNER JOIN PLAYERTYPE T ON P.PLAYER_ID = T.PLAYER_ID
INNER JOIN PLAYER_CONTACT C ON T.PLAYER_ID = C.PLAYER_ID 
INNER JOIN CONTACT_EMAIL E ON E.CONTACT_ID = C.CONTACT_ID
INNER JOIN COUNTRY_TABLE CY ON P.COUNTRY_ID = CY.COUNTRY_ID
WHERE CY.COUNTRY_CODE='AUS'
AND T.PLAYER_TYPE IN ('NEW', 'EXE')
GROUP BY T.FIRST_NAME, T.LAST_NAME, E.EMAIL_ID, CY.COUNTRY_ID 

Here is the fiddle.

CodePudding user response：

Try using DISTINCT LOWER:

SELECT DISTINCT T.FIRST_NAME, 
                T.LAST_NAME, 
                LOWER(E.EMAIL_ID) AS EMAIL_ID, 
                CY.COUNTRY_ID 
FROM PLAYER P
INNER JOIN PLAYERTYPE T ON P.PLAYER_ID = T.PLAYER_ID
INNER JOIN PLAYER_CONTACT C ON T.PLAYER_ID = C.PLAYER_ID 
INNER JOIN CONTACT_EMAIL E ON E.CONTACT_ID = C.CONTACT_ID
INNER JOIN COUNTRY_TABLE CY ON P.COUNTRY_ID = CY.COUNTRY_ID
WHERE CY.COUNTRY_CODE='AUS' AND T.PLAYER_TYPE IN ('NEW', 'EXE')

Output:

Check the demo here.

CodePudding user response：

The repeated errors seem to have different email addresses, or the same email address with different cases. Try removing the email column or simply applying a Lowercase() to the email (or whatever the equivalent is for it in your db engine)