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
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.
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!
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