CODEWITHSUNDEEP
mysqljoinouter-join
Muhammad Gelbana
Muhammad Gelbana

Reputation: 4010

Using MySQL join to show unrelated records

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.

Upvotes: 0

Views: 274

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

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.

Upvotes: 1

EduardoSaverin
EduardoSaverin

Reputation: 545

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

Upvotes: 1

Thomas Ruiz
Thomas Ruiz

Reputation: 3661

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

Upvotes: 0

Axel Amthor
Axel Amthor

Reputation: 11106

what about 

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

Upvotes: 0

Related Questions