CODEWITHSUNDEEP
mysql
amp
amp

Reputation: 12352

MySQL query: retrieve some rows based on keys in another table

I'm new at MySQL, so I will use an example to expose my problem...

I have two tables:

Table1:

+----+-----+-----+-----+-----+
| id | a1  | b1  | c1  | d1  |
+----+-----+-----+-----+-----+
|  1 | ... | ... | ... | ... |
|  2 | ... | ... | ... | ... |
|  3 | ... | ... | ... | ... |
|  4 | ... | ... | ... | ... |
|  5 | ... | ... | ... | ... |
|  6 | ... | ... | ... | ... |
+----+-----+-----+-----+-----+

Table2:

+----+-----+-----+-----+---------------+
| id | a2  | b2  | c2  | table1_row_id |
+----+-----+-----+-----+---------------+
|  1 | ... | ... | ... | ...           |
|  2 | ... | ... | ... | 4             |
|  3 | ... | ... | ... | ...           |
+----+-----+-----+-----+---------------+

Table2 column table1_row_id is the key for table1.

Which kind of query should I use to retrieve table1 data that is not referenced in table2.table1_row_id column?

For instance, for the above tables, the query should return a table like:

+----+-----+-----+-----+-----+
| id | a1  | b1  | c1  | d1  |
+----+-----+-----+-----+-----+
|  1 | ... | ... | ... | ... |
|  2 | ... | ... | ... | ... |
|  3 | ... | ... | ... | ... |
|  5 | ... | ... | ... | ... |
|  6 | ... | ... | ... | ... |
+----+-----+-----+-----+-----+

Probably this is simple, but I don't know how should I do yet...

Upvotes: 1

Views: 339

Answers (2)

Tomas Camin
Tomas Camin

Reputation: 10086

SELECT * 
FROM t1 
WHERE 
    `id` NOT IN (SELECT `table1_row_id` 
                 FROM t2 
                 WHERE
                     `table1_row_id` IS NOT NULL)

Upvotes: 3

Nesim Razon
Nesim Razon

Reputation: 9794

select t1.a1, t1.b1, t1.c1, t1.d1
from table1 t1
left join table2 t2 on t2.table1_row_id = t1.id
where t2.id is null

Upvotes: 1

Related Questions