Since i'm a poor sql developer, i need support to write a sql query for the following scenario (just a simplified example of my situation):
i've got 3 tables, say employe table,department table and companybranch table. the dept column , on the employe table is a fk on the department table; the branch column on t开发者_如何学Che department table is a fk on the companybranch table. Finally more employee are "marked" with the same value . There's a way to select all employes with the same "mark" and, in the same query, check that they work in the same company branch ?thank you in advance
Stefano
Something like this would work:
SELECT *
FROM employee e
JOIN department d ON e.dept = d.id
JOIN companybranch b ON d.branch = b.id
WHERE e.mark = 'mark here'
AND b.id = 'Branch id here'
EDIT
if you can't filter by branch, cause you don't know its value, then you can make a DISTINCT query, to check the differents b.id
SELECT DISTINCT b.id branch_id
FROM employee e
JOIN department d ON e.dept = d.id
JOIN companybranch b ON d.branch = b.id
WHERE e.mark = 'mark here'
this is another option, you can check
SELECT inside.branch_id, COUNT(*) total
FROM (
SELECT b.id branch_id
FROM employee e
JOIN department d ON e.dept = d.id
JOIN companybranch b ON d.branch = b.id
WHERE e.mark = 'mark here'
) inside
GROUP BY inside.branch_id
精彩评论