Need some help with a MySQL SELECT query
I have 3 tables
[contact]
------------------
cID (PRIMARY KEY, INT) || name || lastupdate (TIMESTAMP)
[phone]
----------------------
cID (FOREIGN KEY, linked to contact.cID) || phonenumber || pID (phone number ID, since each contact can have many numbers)
[email]
------------------------
cID (FOREIGN KEY) || email
I need to make a select query that will fetch all the cID of each contact, their name, their phone number if the pID is 1 and their email all in one table. So the output would look like this for example.
cID | name | phone1 | email
----------------------------------------------
45 | John Smith | 1234567 | john.s@test.com
46 | Darth Vader | 9999999 | vader@deathstar.org
47 | Yoda | 1236547 |
-----------------------------------------------------------------------------
I tried doing it like this
SELECT contact.cID, name, phone, email FROM contact, phone, email
WHERE contact.cID = phone.cID AND contact.cID = email.cID AN开发者_Go百科D phone.pID = 1;
This almost does it, but if a contact doesn't have either a phone number or an email by their name then they'll be left out.
I need to show all the contacts, regardless weather they have an email or a phone number in the database.How can I accomplish this?
Select c.cID, C.name, P.phone As phone1, E.email
From contact As C
Left Join phone As P
On P.cID = C.cID
And P.pID = 1
Left Join email As E
On E.cID = C.cID
In short, you need to put the criteria for the pID = 1
in the ON clause when joining phone
to contact
. In additon, you need to use Left Joins to handle the case where they do not have a phone or email.
精彩评论