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

Question

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.

Yaakov Ellis · Accepted Answer · 2013-02-26 20:28:21Z

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)

wow, fantastic. Thank you so much! I'm still not quite getting how I would group if I had more than one Kate however..
If you have more than one Kate, and you only want to show one row for each unique name, then Group By e.firstName. If you want to show one row for each unique employee, even if employees have the same name, then Group By e.Id.
SELECT Distinct e.firstName FROM employee e JOIN account a ON a.salesPersonID1 = e.id Group By e.Id gives me the error: ERROR: Column 'employee.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. :/
So make that Select e.firstName FROM employee e JOIN account a ON a.salesPersonID1 = e.id Group By e.Id, e.FirstName (dont need the distinct anymore if you are grouping)

Dhanya Raj · Accepted Answer · 2013-02-27 10:10:16Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related