What is wrong with this database query?

Posted by outsyncof on Stack Overflow See other posts from Stack Overflow or by outsyncof
Published on 2010-03-12T05:11:57Z Indexed on 2010/03/12 5:17 UTC
Read the original article Hit count: 342

Filed under:
|

I have the following tables in a database (i'll only list the important attributes):

Person(ssn,countryofbirth)
Parents(ssn,fatherbirthcountry)
Employment(ssn, companyID)
Company(companyID, name)

My task is this: given fatherbirthcountry as input, output the names of companies where persons work whose countryofbirth match the fatherbirthcountry input.

I pretend that the fatherbirthcountry is Mexico and do this:

SELECT name 
FROM Company 
WHERE companyid = (SELECT companyid  
                   FROM Employment 
                   WHERE ssn = (SELECT ssn 
                                FROM Person 
                                WHERE countryofbirth = 'Mexico');

but it is giving me an error:

>Scalar subquery is only allowed to return a single row.

am I completely off track? Can anybody please help?

© Stack Overflow or respective owner

Related posts about database

Related posts about database-queries