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

Try using IN instead of =

When you write:

select a from T where a = ( select....)

The sub-query must return a single value. In case if it returns multiple values, you get your error.

To solve this we use the IN operator which allows the sub-query to return a set of value (>=0) and your where condition succeeds if a equals any one of those values.

select a from T where a IN ( select....)

Answer 2

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 3

You should use In in the where condition since the (SELECT ssn FROM Person WHERE countryofbirth = 'Mexico'); may return multiple ssn values.

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

Answer 4

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');

Answer 5

See if this works

SELECT c.Name FROM PERSON p
LEFT JOIN Employment e ON p.ssn=e.ssn LEFT JOIN Company c ON e.CompanyID=c.CompanyID WHERE p.countryofbirth=

Answer 6

The error is due to the fact that the one of the two subqueries are returning multiple rows. I would think it likely that you have multiple people born in Mexico for example.

Select Name
From  Companies
Where Exists(
            Select 1
            From Employment
                Join Person
                    On Person.SSN = Employment.SSN
                Join Parents
                    On Parents.SSN = Person.SSN
            Where Parents.FatherBirthCountry = Person.CountryOfBirth
                And Parents.FatherBirthCountry = @InputParam
                And Employment.CompanyId = Companies.CompanyId
            )

Answer 7

Use:

SELECT c.name
  FROM COMPANY c
  JOIN EMPLOYMENT e ON e.companyid = c.companyid
  JOIN PERSON p ON p.ssn = e.ssn
               AND p.countryofbirth = 'Mexico'

Answer 8

Ideally use the answer from OMG Ponies using JOINs.
But if you do not like JOINs for whatever reason, then TOP 1 should do the trick for you:

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