Suppose I've got a table whose function is particularly to link two other tables when it comes to OOP
So guess that I've two tables: one for person's title and the other one for telephone numbers
Table 1: id person's name 1 John 2 Smith Table 2: id Phone number 5 23424224 6 23424242
After which I've got a third table that links the individual as well as their particular telephone numbers:
Table 3: id person-id phone-number-id 1 1 5 2 2 6
Hence John has telephone number 23424224 and Cruz has telephone number 23424242
And I wish to run an SQL query to fetch all persons from Table 1 whose telephone number begin with let us say (234)
Wouldso would Time passes about connecting the choose queries in this particular table structure...what query would I run?
Thanks ahead of time
First, the only real reason to achieve that table is for those who have a many-to-many relation. While of your life many telephone numbers, can definitely one telephone number have numerous persons? If that's true, your schema implements that requirement, but that appears just a little over-designed in my experience :-)
Second, this can be a quite simple join. What for you to do is first choose the telephone numbers under consideration, then considering that, choose the person IDs in the third table, then considering that, choose what they are called in the first table. Something similar to:
Choose t1.title as title, 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 may also rewrite the "blah.id = blah.id" like a join if you want outer join semantics (include certain fields with NULLs).
I have assumed column names for person's title and telephone number, and assumed you need to return a listing of people that have any telephone numbers beginning 234 (instead of that all their telephone 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 must admit to simply knowing MS-SQL so potentially I'll have added some platform-specific features, tell me for those who have any problem.
Alternatively you could utilize an inner join instead of an "in", but by doing this feels as though it more carefully describes the preferred effect. Plus it eliminates needing to inflict "distincts" to prevent getting someone with 2 telephone numbers beginning 234 showing up two times.
For amounts to someone:
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 some 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
choose person.id, person.title from
table1 as person,
table2 as amounts,
table3 as link
and amounts.phonenumber like '234%'
could be something similar to this, using standard SQL, here where are utilizing a inner join towards the persons table to obtain the title, then another join towards the resulting subquery (blocking because of your phone pattern) to obtain the persons matching amounts
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