• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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)
0
chris rrr
Asked:
chris rrr
  • 3
  • 2
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
chris rrrsoftware/pipelineAuthor Commented:
Much Appriciated !!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now