How to do a MySQL select query on two tables linked by another table

Question

Suppose I have a table whose function is specifically to link two other tables in terms of OOP.

Suppose that I have two tables: one for person's name and another one for phone numbers:

Table 1:
id   person's name
1    John
2    Smith

Table 2:
id   Phone number
5     23424224
6      23424242

And then I have a third table that links the person and their respective phone numbers:

Table 3:
id    person-id    phone-number-id
1         1           5
2         2           6

Hence John has phone number 23424224 and Smith has phone number 23424242.

And I want to run an SQL query to fetch all persons from Table 1 whose phone number start with, let's say, (234).

How would I go about linking the select queries within this table structure...what query would I run?

Answer 1

First, the only reason to do that table is if you have a many-to-many relation. While a person can have many phone numbers, can really one phone number have many persons? If that is true, then your schema implements that requirement, but that seems a little over-engineered to me :-)

Second, this is a fairly simple join. What you want to do is first select out the phone numbers in question, then given that, select out the person IDs from the third table, then given that, select the names from the first table. Something like:

SELECT t1.name as name, t2.number from table1 t1, table2 t2, table3 t3 where t2.number like '234%' and t3.personid = t1.id and t3.phoneid = t2.id;

You can also rewrite the "blah.id = blah.id" as a join if you need outer join semantics (include certain fields with NULLs).