justijn justijn - 17 days ago 6
MySQL Question

Select from table if other table points to record

I have two tables in my database

--------- ---------
| A | | B |
|---------| |---------|
| id | | id |
| name | | a_id |
--------- | name |
---------


I want to select all the records in table
A
only if there is a record in table
B
that points to the record in table
A
(
B.a_id = A.id
).

How can i achieve this? And I'd like an explanation so I understand how it's done.

Answer

Use an INNER JOIN:

SELECT DISTINCT A.*
FROM A
    INNER JOIN B ON B.a_id = A.id

Because of the INNER JOIN, for each row in table A, MySQL finds all the rows from B that match the JOIN condition (B.a_id = A.id).

The SELECT clause instruct it to return only the columns from table A (A.*) but because of the JOIN, a row in A can match more than one row in B. The DISTINCT clause takes care to avoid having the same output row multiple times in the result set.

Comments