Oracle SQL select from multiple tables and join them. Looking to find the players Full Name, Email Address, Player Number, COUNTRY_ID who are from Australia where the player type = NEW OR EXE

SELECT T.FIRST_NAME, T.LAST_NAME, E.EMAIL_ID, PN.PLAYER, C.COUNTRY_ID FROM PLAYER_TYPE T LEFT JOIN PLAYER_CONTACT C ON T.PLAYER_ID = C.PLAYER_ID RIGHT JOIN PLAYER_CONTACT C ON P.CONTACT_ID = C.CONTACT_ID AND C.COUNTRY_CODE='AUS' AND T.PLAYER_TYPE = 'NEW' OR 'EXE'

Below is the expected output

    MARK CLARKSON  [email protected]  480 04  
   CATH SPEARS [email protected] 481 04
   FESS LOPEZ [email protected] 482 04
   FEXS LOPEZ   [email protected] 483 04
   EOVA SMITH  [email protected] 493 04

Here is the SQL fiddle

https://dbfiddle.uk/LPzlOcwB

CodePudding user response：

T.PLAYER_TYPE = 'NEW' OR 'EXE' is not valid syntax and you can use INNER JOIN rather then an OUTER JOIN and need to join all the tables rather than joining player_contact twice.

Like this:

SELECT t.first_name || ' ' || t.last_name AS full_name,
       e.email_id,
       p.player_number,
       p.country_id
FROM   playertype t
       INNER JOIN player p
       ON (p.player_id = t.player_id)
       INNER JOIN country_table c
       ON (p.country_id = c.country_id)
       INNER JOIN player_contact pc
       ON (p.player_id = pc.player_id)
       INNER JOIN contact_email e
       ON (pc.contact_id = e.contact_id)
WHERE  player_type IN ('NEW', 'EXE')
AND    c.country_name = 'AUSTRALIA'

Which, for your sample data, outputs:

fiddle