CODEWITHSUNDEEP
mysql
MichaelFromMars
MichaelFromMars

Reputation: 81

MySQL: SELECT from three tables?

I have problems with a MySQL query with three tables. I would like to search for a name and get all (even better only the first one) phonenumber and email. Here are my tables:

Table 1, contact
==========
id | name
==========
1  | stefan
2  | michael
3  | andy
4  | bob

Table 2, phone
==============================
id | contact_id | phonenumber
==============================
1  | 1          | +1 434 434232
2  | 1          | +1 434 24234
3  | 2          | +1 89234
4  | 4          | +1 345345
5  | 4          | +1 434 7567567

Table 3, email
===============================
id | contact_id | emailaddress
===============================
1  | 1          | [email protected]
2  | 1          | [email protected]
3  | 1          | [email protected]
4  | 4          | [email protected]
5  | 2          | [email protected]

And this is my query, which seams to send MySQL to nirvana:

SELECT c.name, p.phonenumber, e.emailaddress
FROM contact AS c
JOIN phonenumber AS p ON c.id = p.contact_id
JOIN email AS e ON c.id = e.contact_id
WHERE c.name = 'michael'

When I do only one join this works fine as:

SELECT c.name, p.phonenumber
FROM contact AS c
JOIN phonenumber AS p ON c.id = p.contact_id
WHERE c.name = 'michael'

Any ideas?

Thanks

Mike

Upvotes: 0

Views: 110

Answers (3)

user3567419
user3567419

Reputation: 11

Try this:

SELECT c.name, p.phonenumber, e.emailaddress
FROM name_of_your_schema.contact AS c
JOIN name_of_your_schema.phone AS p ON c.id = p.contact_id
JOIN name_of_your_schema.email AS e ON c.id = e.contact_id
WHERE c.name = 'stefan'
LIMIT 1;

Tom L.

Upvotes: 1

ptrk
ptrk

Reputation: 1840

To get just one result per contact, you might use aggregation in a bit unorthodox way. I modified @Emanuel Saringan's query:

SELECT c.name, min(p.phonenumber), min(e.emailaddress)
FROM contact c
left JOIN phone p ON c.id = p.contact_id
left JOIN emailaddress e ON c.id = e.contact_id
WHERE c.name = 'michael'
GROUP BY c.id

See it work here: http://sqlfiddle.com/#!2/6a8700/2

Upvotes: 0

user2422457
user2422457

Reputation:

Try

SELECT c.name, p.phonenumber, e.emailaddress
FROM contact c
INNER JOIN phone p ON c.id = p.contact_id
INNER JOIN email e ON p.contact_id = e.contact_id
WHERE c.name = 'michael'

Upvotes: 0

Related Questions