We help IT Professionals succeed at work.

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

chris rrr
chris rrr asked
on
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)
Comment
Watch Question

Top Expert 2008

Commented:
try this

SELECT
contacts.id,
pnum.id,
contacts.name as "Name",
num as "PhoneNum",
FROM contacts LEFT JOIN pnum ON pnum.cid = contacts.id;
Top Expert 2007

Commented:
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

chris rrrsoftware/pipeline

Author

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

chris rrrsoftware/pipeline

Author

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

Top Expert 2008
Commented:
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
chris rrrsoftware/pipeline

Author

Commented:
Much Appriciated !!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.