JOIN SQL

[aseel_1]

Hi all ,

i have 2 tables

first one called emp have 2 columns id and name

second one called dep have id and empid and nameOfDep

if i want to list all emp that have X dep (But they don't have) Y dep

how i can make it ?

i think i should using JOIN or more one but i can't make the correct query :(

[hericles]

For a start you database design could be better. You should have a table for employees, a table for department and a linking table that holds just employee IDs and department IDs to show who is in what.

emp (id, name)
dep (depid, dep_name)
emp_dep(empid, depid)

Now you join all three (if you want to search by name) or just emp and emp_dep if searching by depid is enough and you can use NOT IN or something similar to separate out your employees.

[tapananand]

Ya your desgin could have been better. In fact @hericles desgin is quite perfect. I assume that X and Y are deptid's. With that design, the query should be:

Select e.id, e.name 
from emp e
where e.id in (Select empid from deptid where deptid=X)
and e.id not in (Select empid from deptid where deptid=Y);

[aseel_1]

Thanks all for your time ^_^
useful comments

[aseel_1]

and if i want to display all dep(son) with emp(father)
every son with father name
nameOfDep empname
?