Mysql complicated join

join;MySQL

I have two tables 

mysql> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Mari  |
|  3 | Sami  |
|  4 | Kate  |
+----+-------+
mysql> select * from dep;
+----+------+-------+
| id | eid  | name  |
+----+------+-------+
|  1 |    1 | Tom   |
|  2 |    2 | Tom   |
|  3 |    1 | Paul  |
|  4 |    1 | Karl  |
+----+------+-------+

dep(eid) is foreign key references emp(id).

Question

  1. What is the query to get all employees who have a son his name is Tom given that s/he doesn't have a son with name Paul (the result is Mari)
  2. What is the query to get all employees who dont have a son his name is Paul (the result is Mari, Sami, and Kate)

I know it can be handled using sub query very easily but i am looking for the join structure

Best Answer

1 . 

select E.* from emp E where id in (
select A.eid from (select distinct eid from dep where name = 'Tom')A
left join (select distinct eid from dep where name = 'Paul')B
on A.eid = B.eid where B.eid is null

)

2 .

select E.* from emp E where id not in (
select distinct eid from dep where name = 'Tom'
)
Related Question