Find the distinct with different string values from SQL select

Question

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:

FIRST_NAME	LAST_NAME	EMAIL_ID	COUNTRY_ID
MARK	CLARKSON	[email protected]	04
MARK	CLARKSON	[email protected]	04
CATH	SPEARS	[email protected]	04
FESS	LOPEZ	[email protected]	04
FEXS	LOPEZ	[email protected]	04
FEXS	LOPEZ	[email protected]	04
EOVA	SMITH	[email protected]	04

Expected Output:

FIRST_NAME	LAST_NAME	EMAIL_ID	COUNTRY_ID
MARK	CLARKSON	[email protected]	04
CATH	SPEARS	[email protected]	04
FESS	LOPEZ	[email protected]	04
FEXS	LOPEZ	[email protected]	04
EOVA	SMITH	[email protected]	04

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.

Answer 1

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:

FIRST_NAME	LAST_NAME	EMAIL_ID	COUNTRY_ID
MARK	CLARKSON	[email protected]	04
CATH	SPEARS	[email protected]	04
FESS	LOPEZ	[email protected]	04
FEXS	LOPEZ	[email protected]	04
EOVA	SMITH	[email protected]	04

Check the demo here.