0

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
0
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.