how to do 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

So 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?

Thanks a lot in advance

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).

Answer 2

I've assumed column names for person's name and phone number, and assumed you want to return a list of people who have any phone numbers beginning 234 (rather than that all of their phone numbers must begin 234).

SELECT 
    persons-name
FROM
    Table1
WHERE
    id IN
        (SELECT
             Table3.person-id
         FROM
             Table3
             INNER JOIN Table2 ON Table2.id = Table3.phone-number-id
         WHERE
             phone-number like '234%')

I have to admit to only knowing MS-SQL so potentially I'll have added some platform-specific features, let me know if you have any trouble.

Alternatively you could use an inner join rather than an "in", but this way feels like it more closely describes the desired effect. Also it avoids having to do any "distincts" to avoid having someone with 2 phone numbers beginning 234 appearing twice.

Answer 3

For numbers to a person:

SELECT persons.name, numbers.phone_number FROM persons
  LEFT JOIN person_number ON person_number.person-id = persons.id
  LEFT JOIN numbers ON person_number.phone-number-id = numbers.id

For persons to a number

SELECT persons.name, numbers.phone_number FROM numbers
  LEFT JOIN person_number ON person_number.phone-number-id = numbers.id
  LEFT JOIN persons ON person_number.person-id = persons.id

Answer 4

select person.id, person.name from
table1 as person,
table2 as numbers,
table3 as link
where person.id=link.person-id
and numbers.id=link.phone-number-id
and numbers.phonenumber like '234%'

Answer 5

would be something like this, using standard SQL, here where are using a inner join to the persons table to get the name, then another join to the resulting subquery (filtering by your phone pattern) to get the persons matching numbers

SELECT T1.fullname, T.phone_number
FROM TABLE3 AS T3
INNER JOIN TABLE1 AS T1
      ON T3.person_id = T1.id
INNER JOIN (
      SELECT phone_id, phone_number
      FROM TABLE2
      WHERE phone_number LIKE '%PATTERN%'
) AS T
     ON T3.phone_id = T.id

Answer 6

First... you don't need column id in Table 3.

Just make personId and phoneId your primary key.

That's the purpose of your many-to-many table (associate multiple phones with the same person). You can have the same person Id for different phone Ids and vice-versa, that is, you can have the same phone Id for different person Ids. You'll never have two rows with the same person Id and phone Id. That makes no sense at all.

With this query you'll get the expected result:

SELECT p.*
FROM Person as p, Phone as ph, PersonPhone as pf
WHERE pf.PersonId = p.Id AND pf.PhoneId = ph.Id and ph.Number like '234%'