Using MySQL join to show unrelated records

Question

I have a table of machines, and a table representing the reachability if these machines across time.

machines
id    name
1     machine1
2     machine2
3     machine3
4     machine4

machines_reachability
machine_id    is_reachable    time
1             0               (whatever)
2             1               (whatever)
3             0               (whatever)
1             1               (whatever)
2             0               (whatever)
3             0               (whatever)
1             1               (whatever)
2             1               (whatever)
3             1               (whatever)

I'm trying to find machines that has NO reachability records (i.e. machine4) using JOINS. This can be done in another ways but I need to do this through joins to have a better understanding of it.

I tried the following

SELECT * FROM machines m LEFT OUTER JOIN machines_reachability mr ON m.id = mr.machine_id

I understand that this should output the whole left table contents (i.e. machines) and the OUTER keyword should exclude the intersection of results between machines and machines_reachability tables based on the condition m.id = mr.machine_id. But that didn't work as I expected. It showed all contents but didn't exclude the rows that didn't match.

So how can I run a JOIN query that actually shows the rows that didn't join whether it's the left table or the right one selectively.

score 1 · Accepted Answer · answered Jun 23 '13 at 15:09

1

Select distinct machines.names where machines natural left outer join machines_rechability where is_reachable is null

answered Jun 23 '13 at 15:09

EduardoSaverin

545
4
19

If I ommit `where is_reachable is null`, I can't find rows with the value of `is_reachable` to be `null`. So how does this condition work in the first place ?! – Muhammad Gelbana Jun 24 '13 at 09:40
If you remove is_reachable is null condition then you will receive a table with Machine1 Machine2 Machine3 Machine4 – EduardoSaverin Jun 24 '13 at 10:29

score 1 · Answer 2 · answered Jun 23 '13 at 15:13

1

Using joins:

select *
from machines m left outer join
     machines_reachability mr
     on m.id = mr.machine_id and
        mr.is_reachable = 1
where mr.machine_id is NULL

The idea is to start with all the machines. The left join keeps all records in the first table, even those that do not match. There is a match in the second table when a machine is reachable (I assume the record has to have the flag set as well as being in the table). The final where clause keeps only machines that have no match in the second table.

answered Jun 23 '13 at 15:13

Gordon Linoff

1,242,037
58
646
786

One thing confusing me, If I do not filter by `mr.machine_id IS NULL`, I get all rows from the `machines` table but not no rows from the `machines_reachability` table with `machine_id` equal to `NULL`. So why does `WHERE mr.machine_id IS NULL` work in the first place ?! – Muhammad Gelbana Jun 24 '13 at 09:38
Ah now I get it. Thank you. – Muhammad Gelbana Jun 24 '13 at 11:35
You and `John smith` answered a correct answer but he is a few minutes earlier and I had to accept his answer to be fair enough. Thanks a lot for your time though :) – Muhammad Gelbana Jun 24 '13 at 11:37

Thomas Ruiz · Answer 3 · 2013-06-23T15:08:25.630

0

SELECT *
FROM machines m
JOIN machines_reachability mr
  ON (m.id <> mr.machine_id)
GROUP BY m.id;

edited Jun 23 '13 at 15:08

answered Jun 23 '13 at 14:59

Thomas Ruiz

3,611
2
20
33

I'm trying to find `machines` that has no information in the `machines_reachability` table. – Muhammad Gelbana Jun 23 '13 at 15:04

score 0 · Answer 4 · answered Jun 23 '13 at 15:03

0

what about

SELECT * from machines where not exists 
( 
      select machine_id from machines_reachability 
      where machines.id = machines_reachability.machine_id 
);

answered Jun 23 '13 at 15:03

Axel Amthor

10,980
1
25
44

I need to do it using `JOIN` – Muhammad Gelbana Jun 23 '13 at 15:04
I'm actually studying `JOINS`, not trying to produce results. – Muhammad Gelbana Jun 23 '13 at 15:10

Using MySQL join to show unrelated records

4 Answers4