sql selection of t1.id, t1.name where t2.id may or may not = t1.id ( hard to ask in 1 line title)

SETUP:
2 tables: C and P (contacts and phone)
C = id, name
P = id, cid, num : where cid corresponds to C.id

PROBLEM:
a contact in C can exist without having any P associated with it. For instance if someone would delete all P's associated with C. But in my current selection I only get C if P.cid = C.id. So now a user can never add a new number to a contact because the contact does't show up from my query. I know how to do this if I select id from C. and then in cgi script go ahead and query each P.cid , but I am sure there is some cleaver way to do this in one query.

QEUSTION:
Is it possible to do this in one QUERY.  I need all C.id and C.name and if P.cid = C.id I want them too.
So it would look like this:
1---Chris----111.222.3333
2---Mike ----444.444.4444
3---Jim   ---- (no phone avail) - <currently won't be returned>
4---Jack ----123.345.5678
5---Jan  ---- (no phone avail) - <currently won't be returned>

CURRENT QUERY WITH PROBLEM:
SELECT
contacts.id,
pnum.id,
contacts.name as "Name",
num as "PhoneNum",
FROM pnum INNER JOIN contacts ON pnum.cid = contacts.id;

it only selects C that have P associated (where P.cid=C.id)
LVL 1
chris rrrsoftware/pipelineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ee_rleeCommented:
try this

SELECT
contacts.id,
pnum.id,
contacts.name as "Name",
num as "PhoneNum",
FROM contacts LEFT JOIN pnum ON pnum.cid = contacts.id;
0
hernst42Commented:
use an outer join in this case. If you add the condition where pnum.num IS NULL you would get all contact which have no number added.
SELECT
contacts.id,
pnum.id,
contacts.name as "Name",
num as "PhoneNum",
FROM pnum LEFT OUTER JOIN contacts ON pnum.cid = contacts.id;

Open in new window

0
chris rrrsoftware/pipelineAuthor Commented:
ee_rlee -
Thanks it totally works. Well one slight typo on my part. ( no "," before FROM ).
I posted a working version below. Could you give me a brief explaination of what LEFT OUTER JOIN is doing so I can understand. I don't like to just copy paste code.

Thanks.

hernst42 -
Your code keeps giving me problems. My actual code that I have to use would look something like this:
I keep simple for the example. I hope my example didn't cause the problems:
contacts.id,
pnum.id,
decode(contacts.name,"xxxxxxxx")  ,
decode(pnum.num, "xxxxxxxx" )
FROM pnum LEFT OUTER JOIN contacts ON pnum.cid = contacts.id;

In fact there is quite a few more fields that I am using, but this should work cover all the ends. If you can fix it, I don't mind sharing some points for you, but like I said it kept giving me errors. at decode(pnum.num, "xxx....
WORKING VERSION FROM ee_rlee 
 
SELECT
contacts.id,
pnum.id,
contacts.name as "Name",
num as "PhoneNum"
FROM contacts LEFT JOIN pnum ON pnum.cid = contacts.id;

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

chris rrrsoftware/pipelineAuthor Commented:
Sorry hernst42 I forgot to post the SELECT at the top line Here is is in a code block
ALMOST WORKING VERSION BY hernst42 
 
SELECT
contacts.id,
pnum.id,
decode(contacts.name,"xxxxxxxx")  ,
decode(pnum.num, "xxxxxxxx" ) 
FROM pnum LEFT OUTER JOIN contacts ON pnum.cid = contacts.id;
 
ERROR:
errors. at decode(pnum.num, "xxx

Open in new window

0
ee_rleeCommented:
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in the 1st table that do not have matches in 2nd table, those rows will not be listed.


The LEFT JOIN returns all the rows from the first table, even if there are no matches in the second table. If there are rows in 1st that do not have matches in the 2nd, those rows also will be listed.

from http://www.w3schools.com/sql/sql_join.asp

you're welcome :D
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chris rrrsoftware/pipelineAuthor Commented:
Much Appriciated !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.