CODEWITHSUNDEEP
sqlsql-server
1252748
1252748

Reputation: 15372

selecting rows the id's of which appear in a column of another table

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.

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.

Upvotes: 2

Views: 1968

Answers (2)

Dhanya Raj
Dhanya Raj

Reputation: 107

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

result:

firstname bill kate

Upvotes: 0

Yaakov Ellis
Yaakov Ellis

Reputation: 41490

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.

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)

Upvotes: 4

Related Questions