including 'is null' brings cartesian result

Sorry if this is not the group for pl/sql questions but here goes..

I have a query that works fine but does not give me all the records I need due to 1 of the columns allowing null. When I include the join statement and include 'or columname' is null the result is a cartesian product.

here is an abbreviated cross view of the data and columnames..

Minimum_wage table

id amount effective_date mw_ind jurisdiction_id variation_id

1,   1.25,   7/1/1965,      1,     1,               null,
2,     1.65, 7/1/1970,      0,      1,                null,
3,   1.75,   7/1/1971,      0,     1,                 87,
4,   1.75,   7/1/1971,      0,     1,                 88,


MINIMUM_WAGE_VARIATION
minimum_wage_variation_id           englishtext
87                                  blah
88                                  blah blah

So when I run the following query I get the non-null values only

SELECT mw.minimum_wage_amount, mw.effective_date, mw.prov_prov_id, mw.mwv_minimum_wage_variation_id,
mwv.minimum_wage_variation_id,mwv.english_text AS variationname FROM MINIMUM_WAGE mw,MINIMUM_WAGE_VARIATION mwv WHERE mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id

AND mw.expiry_date is null
AND mw.minimum_wage_ind = 1
AND mw.jt_jurisdiction_type_id = 2

however when I add the is null I get 2800 records instead of the 36 I expect..

SELECT mw.minimum_wage_amount, mw.effective_date, mw.prov_prov_id, mw.mwv_minimum_wage_variation_id,
mwv.minimum_wage_variation_id,mwv.english_text AS variationname FROM MINIMUM_WAGE mw,MINIMUM_WAGE_VARIATION mwv WHERE mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id

OR (mw.mwv_minimum_wage_variation_id is NULL)
AND mw.expiry_date is null
AND mw.minimum_wage_ind = 1
AND mw.jt_jurisdiction_type_id = 2

What am I missing???

thx.

Dave Received on Wed Mar 02 2005 - 13:41:37 CST