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 :(

3 Years
Discussion Span
Last Post by aseel_1

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.


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);

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.