What is wrong with this database query?

Question

I have the following tables in the database (I will list only the important attributes):

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

My task is this: taking into account the parental homeland as input, output the names of the companies in which people work, whose native origin corresponds to the entry of the father-father.

I pretend my father is Mexico and does this:

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

but this gives me an error:

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

Am I completely unaware? Can anybody help?

Answer 1

The problem is that your subqueries return multiple results, so you need 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 request to this

SELECT name FROM WHERE companyid IN (SELECT companyid
FROM WORK WHERE ssn IN (SELECT ssn On behalf of WHERE countryofbirth = 'Mexico');

Answer 3

Using:

 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 4

You should use In in where , because (SELECT ssn FROM Person WHERE countryofbirth = 'Mexico'); can 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 5

Try using IN instead of =

When you write:

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

The subquery must return a single value. If it returns multiple values, you will get your error.

To solve this problem, we use the IN operator, which allows the sub-query to return a set of values ​​(> = 0) and your condition, if the condition is met, if a is equal to any of these values.

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

Answer 6

See if it 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 7

The error is due to the fact that one of the two subqueries returns multiple rows. I think that you probably have several 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 8

Ideally use OMG Ponies answer with JOIN s.
But if you don't like JOIN for any 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');