这个数据库查询有什么问题？

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?

我完全偏离了轨道吗？有人可以帮忙吗？

8 个解决方案

#1

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

问题是您的子查询返回多个结果，因此您必须使用in =的位置。

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

将ssn =更改为ssn in，以及whereid = where where where in where。

#2

try using the IN keyword not '='.

尝试使用IN关键字而不是'='。

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

SELECT name FROM Company WHERE companyid IN（SELECT companyid FROM Employment WHERE ssn IN（SELECT ssn FROM Person WHERE countryofbirth ='Mexico'）;

#3

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'

#4

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

你应该在where where条件中使用In（SELECT ssn FROM Person WHERE countryofbirth ='Mexico'）;可能会返回多个ssn值。

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

#5

Try using IN instead of =

尝试使用IN而不是=

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.

为了解决这个问题，我们使用IN运算符，它允许子查询返回一组值（> = 0），如果a等于其中任何一个值，则where条件成功。

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

#6

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=

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 =

#7

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
            )

#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:

理想情况下使用来自OMG Ponies的答案使用JOIN。但是如果你因为某种原因不喜欢JOIN，那么TOP 1应该为你做到这一点：

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

#1