Join with 3 tables

[paljnad]

Hello,

I have a query that I run on Oracle 9i that involves 3 tables.

Table Master
Table JHistory
Table GTNC

The primary keys in the above 3 tables are all different but there are parts of the primary key that are common to all 3 tables.

Here is my query:

select DISTINCT b.FPS_NUMBER as FPS_NUMBER,
a.SHRS_CODE,
a.ID,
a.LAST_NAME,
a.FIRST_NAME,
a.M_TYPE,
a.START_DATE,
a.END_DATE,
a.E_TYPE,
a.H_D_R_SWITCH,
a.REP_TO_NAME,
to_char(c.e_week_hours, '00') AS e_week_hours,
to_char(c.e_week_mins, '00') AS e_week_mins,
c.e_week_indicator
from MASTER a, GTNC b, JHISTORY c
WHERE a.SHRS_CODE = b.SHRS_CODE(+)
AND a.ID = b.ID(+)
and a.M_TYPE = b.M_TYPE(+)
and a.shrs_code = c.shrs_code
and a.ID = c.ID
and a.m_type = c.m_type
and c.sequence_number = 1
and a.last_name like '%ABC%'



Now, ABC exists in the master table and the GTNC table but not the JHISTORY table. XYZ exists in the master table and the JHISTORY table but not in the GTNC table. And there are multiple records for each person in the JHISTORY table and we want the record where the sequence number is 1.

So when I run the query for ABC then I get a row back but when I run the query for XYZ then I dont get anything back.

How do I join these tables?

 

[SantaMufasa]

So when I run the query for ABC then I get a row back but when I run the query for XYZ then I dont get anything back.

Could it be because your WHERE clause allows only "ABC" records in the result set:

...and a.last_name like '%ABC%'

...Or did I misunderstand your question?

If I misunderstood, then please post three "CREATE TABLE..." statements (one for each of your three join tables), and post enough "INSERT INTO..." statements into the three tables to adequately show all data conditions to exercise the code situations we must solve for.

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. The cost will be your freedoms and your liberty.”

 

[Dagon]

I don't see how your example query returns a row for 'ABC' if there is no row in JHISTORY because you are not outer-joining to JHISTORY. I don't see a lot of problem outer-joining to both tables. You should be able to do:

select DISTINCT b.FPS_NUMBER as FPS_NUMBER,
                a.SHRS_CODE,
                a.ID,
                a.LAST_NAME,
                a.FIRST_NAME,
                a.M_TYPE,
                a.START_DATE,
                a.END_DATE,
                a.E_TYPE,
                a.H_D_R_SWITCH,
                a.REP_TO_NAME,
                to_char(c.e_week_hours, '00') AS e_week_hours,
                to_char(c.e_week_mins, '00') AS e_week_mins,
                c.e_week_indicator
  from MASTER a, GTNC b, JHISTORY c
 WHERE a.SHRS_CODE = b.SHRS_CODE(+)
   AND a.ID = b.ID(+)
   and a.M_TYPE = b.M_TYPE(+)
   and a.shrs_code = c.shrs_code (+)
   and a.ID = c.ID (+)
   and a.m_type = c.m_type (+)
   and c.sequence_number (+) = 1
   and a.last_name like '%ABC%'

If your query is actually more complex than just sequence number on c and actually requires a value based on a most recent date or something like that, then you could use an inline view.

 

[cmmrfrds]

I find it easier to read and code by using the ANSI join syntax and put the join criteria in the joins and leave the where clause for the selection criteria.

select DISTINCT b.FPS_NUMBER as FPS_NUMBER,
a.SHRS_CODE,
a.ID,
a.LAST_NAME,
a.FIRST_NAME,
a.M_TYPE,
a.START_DATE,
a.END_DATE,
a.E_TYPE,
a.H_D_R_SWITCH,
a.REP_TO_NAME,
to_char(c.e_week_hours, '00') AS e_week_hours,
to_char(c.e_week_mins, '00') AS e_week_mins,
c.e_week_indicator

from MASTER a
Left join GTNC b
ON a.SHRS_CODE = b.SHRS_CODE
AND a.ID = b.ID
and a.M_TYPE = b.M_TYPE
Left join JHISTORY c
ON a.shrs_code = c.shrs_code
and a.ID = c.ID
and a.m_type = c.m_type
and c.sequence_number = 1
WHERE (a.last_name like '%ABC%'
or (a.last_name like '%ABC%' and c.yourprimaykey is null))