help with select 1

[mufka]

I'm trying to work out a select statement that will pull data from a few different tables based on common id's accross the tables.

My test code is:

select 
     c.firstname||' '||c.lastname, 
     d.dept_name, 
     s.salary_level, 
     su.supervisor_name,
          from contact c, dept d, salary s, supervisor su
          where c.id=su.c_id
          and d.dept_id=c.dept_id
          and s.salary_id=c.salary_id
    order by c.lastname
;

In this case, the four tables are contact, dept, salary and supervisor. The problem that I am having is that I want to list all first and last names, but some don't have departments assigned so with the current select statement, those entries would be excluded. How can I adjust my select to still list those that don't have departments but maybe put a - where no department exists?

 

[SantaMufasa]

Mufka,

As you already have identified, the reason you don't see CONTACTS that are missing DEPT_ID values is because you specified that the result set should includes only those rows "WHERE...and d.dept_id=c.dept_id"...that is, each CONTACT in the result set must "partner" with a matching DEPT_ID.

If we want CONTACTS with NULL for DEPT_ID to "partner" with a DEPARTMENT (so that such a CONTACT will be part of the result set), then we must signify that we want a "ghost" DEPARTMENT to match up with CONTACTS that contain NULL in the DEPT_ID.

An operator that we can use in Oracle to signify "ghost" records to "match" real records is a "tombstone" (that's my term). Here is a "tombstone" operator: "(+)"...you know...a headstone with a cross on it? <grin>

So here's the code you need to get your results, complete with the "tombstone" operator to "join" rows that would otherwise be "left out" of the results...(We call such a matching process a "left outer join"):

select 
     c.firstname||' '||c.lastname, 
     d.dept_name, 
     s.salary_level, 
     su.supervisor_name,
          from contact c, dept d, salary s, supervisor su
          where c.id=su.c_id
          and d.dept_id[B][I](+)[/I][/B]=c.dept_id
          and s.salary_id=c.salary_id
    order by c.lastname
;

Let us know your results, and thoughts, on this method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”