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

4
  • SUM(name = 'Name') > 0 (if is) or = 0 (if is not).
    – Akina
    Aug 15, 2018 at 13:10
  • 2
    But, Mari does have a son named Tom, as established in the first answer....
    – RDFozz
    Aug 15, 2018 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, 2018 at 15:04
  • For the second question. The result should be only Sami and Kate
    – AMG
    Aug 15, 2018 at 16:00

3 Answers 3

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
  • 1
    You are right! I didn't notice it was MySQL. Query fixed to fit MySQL. Thanks for the feedback!
    – AMG
    Aug 16, 2018 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
  • 1
    Oh, my bad. Thanks for correcting me. I edited the question to reflect this. Aug 16, 2018 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;
    
2
  • 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, 2018 at 14:53
  • 1
    I also think the query doesn't return the required result.
    – AMG
    Aug 15, 2018 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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