Access all rows in table that is linked to another table

Question

I have 2 tables in SQLite. One is called contacts and the other is phoneNumbers. phoneNumbers is linked to contacts with integer references. Here is the tables:

CREATE TABLE contacts(
    id INTEGER PRIMARY KEY,
    name text
);

CREATE TABLE phoneNumbers(
    id INTEGER PRIMARY KEY,
    homePhone text,
    contact_id INTEGER REFERENCES contacts(id)
);

My question is, how can I access all homePhone that is linked to contacts (id) 1?

Hope this is clear. If you have any questions, feel free to ask in the comments.

`SELECT * FROM phoneNumbers WHERE contact_id = 1` doesn't work for you? — Phil, Mar 26 '15 at 22:55

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

Here you have it:

SELECT homePhone 
FROM phoneNumbers 
JOIN contacts
ON homePhone.contact_id=contacts.id
WHERE contacts.id=1

And never use SELECT * : https://stackoverflow.com/a/3639964/1919749

edited May 23 '17 at 12:06

Community

1
1

answered Mar 26 '15 at 22:56

ismaestro

6,172
6
33
47

Why bother with the join? – Phil Mar 26 '15 at 23:02
Can you explain `JOIN` and `ON homePhone.contact_id=contacts.id`? – Mar 26 '15 at 23:03
2

It binds the two tables together based upon the primary and foreign key relation. It is useful if you want to use additional columns from both tables. – WorkSmarter Mar 26 '15 at 23:05
@WorkSmarter Thats it! – ismaestro Mar 27 '15 at 07:38

Access all rows in table that is linked to another table

1 Answers1