Mysql – SELECT same columns of Foreign table multiple time

MySQLsubquery

I have tables as follows:

ORG

    id  Name     media_budget  p_contact  s_contact  
------  -------  ------------  ---------  -----------
     1  FIFA          4654623          1            3
     2  FIFA_AP          1234          2            3
     3  ICC            203254          3            2

USERS

    id  fname    lname   contact  email            
------  -------  ------  -------  -----------------
     1  JHONE    MICHEL   124552  email@gmail.com  
     2  William  Martin   254623  gmail@email.com  
     3  Ann      Lee      321546  lee@gmail.com

I want to SELECT user.fname for ORG.p_contact and ORG.s_contact for each row in ORG, following statement works fine with single record in ORG however, when query return multiple rows it throws an error 

"Error Code: 1242
Subquery returns more than 1 row"

my statement is:

SELECT o.Name, o.media_budget,
(SELECT u.fname AS primaryName    FROM org o LEFT OUTER JOIN users u ON o.p_contact = u.id) AS PrimaryContact,
(SELECT usr.fname AS SecondaryName  FROM org og LEFT OUTER JOIN users usr ON og.s_contact = usr.id) AS SecondaryContact
 FROM org o;

What should be changed in sub-query to achieve desire result :

    id  Name    media_budget  PrimaryContact      SecondaryContact  
------  ------  ------------  --------------      ----------------
     3  ICC           203254          Ann            William
     4  FIFA            2656          JOHN            Ann

Best Answer

as showing in question - no chances (You show result exactly for rows not presented in source tables), but:

SELECT 
    o.id,
    o.Name, 
    o.media_budget,
    u.fname AS PrimaryContact,
    usr.fname AS SecondaryContact
FROM org o
JOIN users u ON o.p_contact = u.id
JOIN users usr ON o.s_contact = usr.id

must work for You

When You use subquery, You must control - subquery must return 0 or 1 records, this is mandatory, for example like this:

SELECT 
    o.id,
    o.Name, 
    o.media_budget,
    (SELECT u.fname AS primaryName    FROM users u WHERE u.id = o.p_contact LIMIT 1) AS PrimaryContact,
    (SELECT usr.fname AS SecondaryName  FROM users usr WHERE usr.id = o.s_contact LIMIT 1) AS SecondaryContact
 FROM org o;
Related Question