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. =.

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

use the IN keyword not '='.

try altering your query for this

Choose title FROM Company WHERE companyid IN (Choose companyid
FROM Employment WHERE ssn IN (Choose ssn FROM Person WHERE countryofbirth = 'Mexico')

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'

You need to use In within the where condition because 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');

Use 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 JOINs.
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');