Newbie SQL: How to fetch rows not included in another table?

Question

I'm having trouble understand JOINs. I have a list of people, and a list of attacks like this:

SELECT * FROM people;
+------+--------+
| id   | name   |
+------+--------+
|    1 | johnny |
|    2 | pete   |
|    3 | sally  |
|    4 | paul   |
|    5 | jen    |
|    6 | alan   |
+------+--------+

SELECT * FROM attacks;
+------------+----------+
| attackerId | victimId |
+------------+----------+
|          1 |        2 |   (johnny attacked pete)
|          1 |        4 |   (johnny attacked paul)
|          1 |        6 |   (johnny attacked alan)
|          3 |        5 |   (sally attacked jen)
+------------+----------+

Could anybody help me figure out what command gets people not attacked by johnny like this?

+-------+
| name  | 
+-------+
| sally |
| jen   |
+-------+

Many thanks!

Hint: `NOT EXISTS`, `NOT IN`, `LEFT JOIN`/`WHERE`. – Gordon Linoff Nov 21 '17 at 12:52 — Gordon Linoff, Nov 21 '17 at 12:52
Another hint: you can join to the same table more than once – Brian Dewhirst Nov 21 '17 at 12:53 — Brian Dewhirst, Nov 21 '17 at 12:53

score 2 · Answer 1 · answered Nov 21 '17 at 12:53

2

You can take a look to this link. Basically is the same question i think

What is the difference between "INNER JOIN" and "OUTER JOIN"?

In your case you want to do an outer join

Hope this helps

answered Nov 21 '17 at 12:53

Francisco Valle

613
10
10

score 2 · Accepted Answer · answered Nov 21 '17 at 13:10

2

SELECT Name FROM People
WHERE ID NOT IN (SELECT VictimID FROM Attacks
                 WHERE AttackerId= 1) AND ID<>1

answered Nov 21 '17 at 13:10

NiveaGM

249
2
11

This works, and the simplest answer, thank you. As a side note I'm guessing that it cannot be achieved without a second SELECT statement, since all of the offered solutions contain one. Does that mean that this particular query will be very slow? – Josh Nov 21 '17 at 13:43
Obviously the simplest answer(already +1'd it). Only problem is that the `id` of the attacker should be known which won't be case in majority of the Real time scenario. – Harshil Doshi Nov 21 '17 at 14:03

Harshil Doshi · Answer 3 · 2017-11-21T13:26:00.013

1

You can use Left join here. Also, you can change the name in subquery and get results for any other attackers.

select p.name
from people p
left join attacks a
on p.id = a.victimId
where 
name <> 'johnny'
and (a.attackerId not in (select id from people
                           where name = 'johnny'
                          )
     or a.attackerId is null
    )
;

Click here for Demo

edited Nov 21 '17 at 13:26

answered Nov 21 '17 at 13:00

Harshil Doshi

3,497
3
14
37

Unfortunately this doesn't work, because if I change "<> 'johnny'" to "<> 'pete'" I would expect to get johnny, sally, paul, jen and alan but I only get three results (jen, johnny, sally). – Josh Nov 21 '17 at 13:36
you need to change the name in subquery too. It'll work. See here :http://sqlfiddle.com/#!9/3dc4f7/14/0 – Harshil Doshi Nov 21 '17 at 13:38

score 0 · Answer 4 · answered Nov 21 '17 at 12:58

0

SELECT * FROM PEOPLE AS P INNER JOIN ATTACKER AS A ON P.Id = A.AttackerId WHERE P.AttackerId != 1;

The above query will return you every row where the attacker is not Id 1(In your case "Johnny").

answered Nov 21 '17 at 12:58

Dev

316
1
4
15

It won't return `sally` – Harshil Doshi Nov 21 '17 at 13:01
1

Downvoted because this answer does not answer the stated requirement. – Dan Bracuk Nov 21 '17 at 13:16
My bad. Indeed, this answer is not complete. The answer of NiveaGM seems more appopriate. – Dev Nov 21 '17 at 15:07

score 0 · Answer 5 · answered Nov 21 '17 at 13:05

Use JOIN and NOT EXISTS.

Query

select `name` from `table_1` ta
where not exists(
    select 1 from (
        select t1.`name` as `attacker`, t3.`name` as `victim`
        from `table_1` t1
        join `table_2` t2
        on t1.`id` = t2.`attackerId`
        join `table_1` t3
        on t3.`id` = t2.`victimId`
    ) tb
    where ta.`name` = tb.`victim`
    and tb.`attacker` = 'johnny'
)
and `name` <> 'johnny';

Find a fiddle demo here

Newbie SQL: How to fetch rows *not* included in another table?

5 Answers5

Newbie SQL: How to fetch rows not included in another table?