SQL to retrieve only if there is no FK reference

Question

I have two tables as shown below they have a one to many relationship. match table (one) message_log table (many) using match_id as a FK

Table 1

enter image description here

Table 2

enter image description here

I'm trying to write an sql statement that would return all the match_ids that do not exist in the message_log table.

possible duplicate of [SQL - find records from one table which don't exist in another](http://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — Palpatim, Jun 09 '15 at 16:00
Please post table definitions and sample data as formatted ***text***, not as screenshots. — , Jun 09 '15 at 16:02

Donal · Accepted Answer · 2015-06-09T16:19:33.173

2

This will give you all the match_ids, in the match table, that do not exist in the message_log table:

SELECT 
m.match_id
FROM match m
WHERE 
m.match_id NOT IN
(
    SELECT
    ml.match_id 
    FROM message_log ml
)

edited Jun 09 '15 at 16:19

answered Jun 09 '15 at 16:04

Donal

score 0 · Answer 2 · answered Jun 09 '15 at 17:39

0

SELECT m.match_id 
  FROM "match" m LEFT OUTER JOIN "message_id" g ON m.match_id = g.match_id
 WHERE g.match_id IS NULL

Try different solutions, as the optimizer will sometimes do interesting things.

answered Jun 09 '15 at 17:39

James Felix Black

2 Answers2