1

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

  • SUM(name = 'Name') > 0 (if is) or = 0 (if is not). – Akina Aug 15 '18 at 13:10
  • 2
    But, Mari does have a son named Tom, as established in the first answer.... – RDFozz Aug 15 '18 at 14:46
  • @RDFozz,,, the question was to have a son named Tom and not to have a son named Paul,,, which satisfies Mari's case – Alaa Aug 15 '18 at 15:04
  • For the second question. The result should be only Sami and Kate – AMG Aug 15 '18 at 16:00
1

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'
)
  • 1
    You are right! I didn't notice it was MySQL. Query fixed to fit MySQL. Thanks for the feedback! – AMG Aug 16 '18 at 14:23
1

1.

SELECT e.*
FROM emp e
INNER JOIN dep d1 ON e.id = d1.eid AND d1.name = 'Tom'
LEFT OUTER JOIN dep d2 ON e.id = d2.eid AND d2.name = 'Paul'
WHERE d2.id IS NULL 

You can also use a set operation to solve this one:

SELECT result.*
FROM (
  (SELECT e.*
  FROM emp e
  INNER JOIN dep d ON e.id = d.eid and d.name = 'Tom')
  MINUS
  (SELECT e.*
  FROM emp e
  INNER JOIN dep d ON e.id = d.eid and d.name = 'Paul')) result

2.

SELECT e.*
FROM emp e
LEFT OUTER JOIN dep d ON e.id = d.eid AND d.name = 'Tom'
WHERE d.id IS NULL 

EDIT: As pointed out by ypercubeᵀᴹ, MySQL doesn't contain the MINUS operator.

  • 1
    Oh, my bad. Thanks for correcting me. I edited the question to reflect this. – Luís Gabriel de Andrade Aug 16 '18 at 16:16
0

I think i found the answer:

  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)

    select E.*,D.*, D2.*
    from emp E
         left join dep D on E.id=D.eid and D.name!='Paul'
         left join dep D2 on D2.eid=E.id and D2.name='Tom'
    where D.name='Tom' and D2.name is null;
    
  2. What is the query to get all employees who dont have a son his name is Tom (the result is Mari, Sami, and Kate)

    select E.*,D.*, D2.*
    from emp E
         left join dep D on E.id=D.eid and D.name!='Paul'
         left join dep D2 on D2.eid=E.id and D2.name='Paul'
    where (D.name!=D2.name and D2.name!='Paul') or D2.name is null;
    
  • Have you tried this? Does it give the result you expect? Why are you checking for the name "Paul" to get results that have nothing to do with Paul (or, are you actually trying to get all employees who don't have a son named Paul)? – RDFozz Aug 15 '18 at 14:53
  • 1
    I also think the query doesn't return the required result. – AMG Aug 15 '18 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.