I've the next tables inside a database (i'll only list the key characteristics):
Person(ssn,countryofbirth) Parents(ssn,fatherbirthcountry) Employment(ssn, companyID) Company(companyID, name)
My task is: given fatherbirthcountry as input, output what they are called of companies where persons work whose countryofbirth match the fatherbirthcountry input.
I pretend the fatherbirthcountry is Mexico and do that:
SELECT name FROM Company WHERE companyid = (SELECT companyid FROM Employment WHERE ssn = (SELECT ssn FROM Person WHERE countryofbirth = 'Mexico');
but it's giving me a mistake:
>Scalar subquery is only allowed to return a single row.
shall we be held completely off target? Can anybody help?
However , your subqueries are coming back multiple results, so make use of
where in versus.
where ssn = to
where ssn in, and
where companyid = to
where companyid in.
use the IN keyword not '='.
try altering your query for this
WHERE companyid IN (Choose companyid
FROM Employment WHERE ssn IN (Choose ssn FROM Person WHERE countryofbirth = 'Mexico')
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'
You need to use
In within the
where condition because the
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');
IN rather than
Whenever you write:
select a from T where a = ( select....)
The sub-query must return just one value. Just in case whether it returns multiple values, you receive your error.
To resolve this we make use of the IN operator which enables the sub-query to come back some value (>=) as well as your where condition works if
a equals any kind of individuals values.
select a from T where a IN ( select....)
Find out if this works
Choose c.Title FROM PERSON p
LEFT JOIN Employment e ON p.ssn=e.ssn LEFT JOIN Company c ON e.CompanyID=c.CompanyID WHERE p.countryofbirth=
The mistake is because of the truth that the among the two subqueries are coming back multiple rows. I'd think it in all probability you have multiple people born in Mexico for instance.
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 )
Ideally make use of the answer from OMG Ponies using
But when you don't like
JOINs for reasons uknown, then
TOP 1 should have the desired effect for you personally:
SELECT name FROM Company WHERE companyid =(SELECT TOP 1 companyid FROM Employment WHERE ssn = ( SELECT TOP 1 ssn FROM Person WHERE countryofbirth = 'Mexico');