What is wrong with this database query?

Question

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?

Answer 1

The problem is that your subqueries are returning multiple results, so you have to use where in vs. =.

Change where ssn = to where ssn in, and where companyid = to where companyid in.

Answer 2

try using the IN keyword not '='.

try changing your query to this

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