FULL OUTER JOIN PROBLEM 1

[jayjaybigs]

Hello,

I have two tables: previous and current.
The two tables have similar data structures.

previous contain 45 records for cp.person_code = 200199.
current contain 46 records for cp.person_code = 200199.

I used full outer join on the two tables expecting at most 46.

Here is my code:
select cp.PERSON_CODE,
cp.FIRST_NAME,
cp.LAST_NAME,
cp.BATCH_CODE,
cp.PC_CODE,
cp.DESCRIPTION,
pp.PREVIOUS_AMOUNT,
cp.CURRENT_AMOUNT,
(pp.PREVIOUS_AMOUNT - cp.CURRENT_AMOUNT ) Difference
from current_pay cp full outer join previous_pay pp
on cp.PC_CODE = pp.PC_CODE
and cp.PERSON_CODE = pp.PERSON_CODE
and cp.CURRENT_COMP_PCCODE = pp.PREVIOUS_COMP_PCCODE
and cp.person_code = 200199
order by 1, 5

However, I am getting 31517 records with some records having NULL values in all all the fields expect for PREVIOUS_AMOUNT(actually lots of records like that).

Also, there are some PC_CODE that exist in previous_pay table and not in current_pay. I was expecting them to show up with this final query by using FULL OUTER JOIN. But they are not.

Please help

 

[dnoeth]

Every condition in the ON-clause is used to do the join, if the join-condition is false, the row is still returned (with NULLs).
*After* the join the WHERE-conditions are used to restrict the result set.

So just move the last condition into the where-clause:
WHERE cp.person_code = 200199

Dieter

 

[jayjaybigs]

Thanks dnoeth, that actually helps to return 46 records which is the number expected.

However,
There are some rows in previous table(which originally has 45 records), which are not in current(originally has 46 records) that are not coming out in the final result.

Please help

 

[r937]

if cp.person_code = 200199 is in the WHERE clause, then any row where pp has no matching cp will be dropped, since NULL in cp.person_code will not match to 200199

i think both cp.person_code = 200199 and pp.person_code = 200199 should be in the ON clause

but i'm on shaky ground here, because i've never done this with a FULL OUTER

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jayjaybigs]

I just found out that the original code was actually giving the right number all along. What is happening is that, because I was selecting most of fields from current_pay cp, hence, when there is value in pp.PC_CODE and no value in pc.PC_CODE, it just put null in all the fields except pp.PREVIOUS_AMOUNT.

My question is how can I get values in all the fieds. Without substituting NULL vlaues.

 

[r937]

are you sure you want a FULL OUTER JOIN?

because that's guaranteed to give you NULLS for unmatched rows from either table

maybe you want an INNER JOIN instead?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jayjaybigs]

How can I use inner join to achieve the same result as OUTER JOING without the NULL values and to get the unmatched rows.

 

[dnoeth]

Rudy is right, you'll have to extend the where clause:

WHERE cp.person_code = 200199
OR pp.person_code = 200199

And you can't use Inner Join, you'll have to use Outer.

To get rid of the NULLs:

select
coalesce(cp.PERSON_CODE, pp.PERSON_CODE),
coalesce(cp.FIRST_NAME, pp.FIRST_NAME),

If you don't want NULLs in difference:
(coalesce(pp.PREVIOUS_AMOUNT, 0)
- coalesce(cp.CURRENT_AMOUNT, 0)) Difference

As your DBMS supports Full Outer Join it's probably not missing CASE/COALESCE, too.

Dieter

 

[jayjaybigs]

thanks everyone