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
- 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)
- 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
SUM(name = 'Name') > 0
(if is) or= 0
(if is not).