Toad World® Forums

LEFT OUTER JOIN question


#1

Hello all,

Having a little problem with a left outer join. I would expect 351 records (from table J) from the query below, but I’m only getting 125 (which is the number of records from table E).

What’s wrong with this join??? It seems so straight forward ugh.

I’m aware of the (+) and that works fine. But I’m trying to figure out why this code doesn’t work…

Thanks in advance!

SELECT

j.JURISDICTIONCODE,

j.NAME,

max(e.ACTION_DT) as TAX_RATE_APPROVED

FROM

DLSGATEWAY.JURISDICTION j left outer join

DLSGATEWAY.EVENT e

on j.jurisdictioncode = e.jur_code

WHERE

e.FISCAL_YEAR IN (2014)

AND j.JURISDICTIONCODE <= 351

and e.process_id > 100

group by

j.JURISDICTIONCODE,

j.NAME,

e.fiscal_year

order by

j.jurisdictioncode


#2

Perhaps your predicate is preventing some records. If you simply do a left outer join on the join column and no restrictions in the predicate then you would get all records from jurisdiction table even though you don’t have a corresponding record in the event table. I would alter the predicate and see where you are dropping those records.


#3

Thanks ssikharam. I took your adivce and moved some things around. Turns out the predicates for the EVENT table should not be in the WHERE clause, but rather immediately following the ON piece of the join using AND. The predicate for the JURISDICTION table goes in a WHERE clause. This logic sounds wrong to me, but the query below is returning my desired results.

The query below returns my desired results.

SELECT

j.JURISDICTIONCODE,

j.NAME,

max(e.ACTION_DT) as TAX_RATE_APPROVED

FROM

DLSGATEWAY.JURISDICTION j left outer join

DLSGATEWAY.EVENT e

on j.jurisdictioncode = e.jur_code

and e.FISCAL_YEAR IN (2014)

and e.process_id > 100

where

j.jurisdictioncode <= 351

group by

j.JURISDICTIONCODE,

j.NAME,

e.fiscal_year

order by

j.jurisdictioncode

Again,

THANKS!


#4

The way you pushed the predicate into the join is the right way and also has an added benefit of some performance. Glad you figured it out.

Cheers.

Suresh