I can't quite get my head around a SQL query because it is not my forté. I'm trying to select the names of rows in an employees table the id's of which appear in a column salesPersonId of another table, accounts. That is, any employee name which is represented in the accounts table.
我不太理解SQL查询，因为它不是我的强项。我试图选择employee表中的行名称，其中id出现在另一个表的列salesPersonId帐户。也就是说，任何在帐户表中表示的员工名称。
ACCOUNT
+----+---------------+
| id | salesPersonID |
+----+---------------+
| 0 | 1020 |
+----+---------------+
| 1 | 1020 |
+----+---------------+
| 2 | 1009 |
+----+---------------+
EMPLOYEE
+------+---------------+
| id | firstName |
+------+---------------+
| 1009 | BILL | <-select his name
+------+---------------+
| 1020 | KATE | <-select her name
+------+---------------+
| 1025 | NEIL | <-not this guy
+------+---------------+
Since Neil hasn't got any presence in account.salesPersonID, I'd like to select the other two besides him. I'm not getting very far with it though, and looking for some input.
因为尼尔还没有出现。售货员:除了他之外，我还想挑选另外两个。但是我并没有得到很好的结果，并且在寻找一些输入。
SELECT * FROM employee e
LEFT JOIN account a
ON a.salesPersonID = e.id
WHERE (SELECT COUNT(salesPersonID) FROM account) > 0
does not work. I wonder how I could select these employee names that are present in salesPersonID. Thank you.
不工作。我想知道如何选择salesPersonID中出现的这些员工名称。谢谢你！
2 个解决方案
#1
4
Try this:
试试这个:
SELECT Distinct e.firstName
FROM employee e
JOIN account a ON a.salesPersonID = e.id
The JOIN will take care of the filtering to make sure that you are only returning the records that exist in both tables.
JOIN将负责过滤，以确保只返回两个表中存在的记录。
Distinct will make sure that you are only getting each firstName value one time. You can also accomplish this by Grouping by employee.Id or employee.firstName (grouping by Id is the better strategy if you want to return one row for each unique employee, even if they have the same first name, grouping on firstName or using distinct is for when you just want one of each unique name, even if the name is used by more than one employee)
不同的将确保您只获得每个firstName值一次。您还可以通过按员工分组来实现这一点。Id或员工。firstName(分组通过Id是更好的策略,如果你想返回一行每一个独特的员工,即使他们有相同的名字,分组firstName或使用不同的是当你只是想要一个每一个独特的名字,即使使用的名字是超过一名员工)
#2
0
u can have the query like this....
这样你可以查询....
select e.firstname from employees1 e left join account a on(e.id=a.salespersonid) where e.id= a.salespersonid group by e.firstname
选择e。从employees1e的第一个名字(e.id=a.salespersonid)中，e。id =。salespersonid group by e.firstname
result:
结果:
firstname bill kate
firstname比尔凯特
#1
4
Try this:
试试这个:
SELECT Distinct e.firstName
FROM employee e
JOIN account a ON a.salesPersonID = e.id
The JOIN will take care of the filtering to make sure that you are only returning the records that exist in both tables.
JOIN将负责过滤，以确保只返回两个表中存在的记录。
Distinct will make sure that you are only getting each firstName value one time. You can also accomplish this by Grouping by employee.Id or employee.firstName (grouping by Id is the better strategy if you want to return one row for each unique employee, even if they have the same first name, grouping on firstName or using distinct is for when you just want one of each unique name, even if the name is used by more than one employee)
不同的将确保您只获得每个firstName值一次。您还可以通过按员工分组来实现这一点。Id或员工。firstName(分组通过Id是更好的策略,如果你想返回一行每一个独特的员工,即使他们有相同的名字,分组firstName或使用不同的是当你只是想要一个每一个独特的名字,即使使用的名字是超过一名员工)
#2
0
u can have the query like this....
这样你可以查询....
select e.firstname from employees1 e left join account a on(e.id=a.salespersonid) where e.id= a.salespersonid group by e.firstname
选择e。从employees1e的第一个名字(e.id=a.salespersonid)中，e。id =。salespersonid group by e.firstname
result:
结果:
firstname bill kate
firstname比尔凯特