[thelist] An SQL query about null and =

[Luther, Ron]

john at johnallsopp.co.uk noted:


> The "a.field1 = b.field1 and a.field2 = b.field1" part would indicate
> that
> a.field1 must equal a.field2.  So, unless they matched you would get
> back no
> results.

>>Would it really? 

Hi John,

Yup. It's the old "if A=C and B=C then A=B" kind of thing.

>>let's imagine it's a database of international flights containing a 
>>from country and a to country. The a table holds the flight record 
>>and wants to look up two country codes to get the full country name. 

Okay. (As Emily Latella used to say "That's verrrrrry different!")

;-)

Got it. Try thinking more like you were using 3 tables instead of 2. 
(Because you really are - you've got two copies of the reference data 
table and the one raw flight data table.) Then it's not so tough:

SELECT a.To_Cntry_Cd, a.From_Cntry_Cd, B.Cntry_Desc AS From_Cntry_Desc, C.Cntry_Desc AS To_Cntry_Desc

FROM flight_data a, reference_country_table b, reference_country_table c 

WHERE a.From_Cntry_Cd = b.Cntry_Cd and a.To_Cntry_Cd = c.Cntry_Cd


(Hint: You are allowed to join in the same table more than once in a 
single select statement. With some dbs and apps you may need to provide 
a unique alias for each copy you link together, but they will still 
let you do it. It's a useful technique.)

If you're still having trouble with null values in one of the fields 
(Hopefully not - I know I certainlywouldn't want to fly to a *null* 
location - oh wait, that's 'Newark' isn't it! Nope, still don't want 
to fly there.) then try LEFT JOINing in the 'b' or 'c' table.


HTH,

RonL.