[thelist] An SQL query about null and =
Luther, Ron
ron.luther at hp.com
Thu Jan 20 14:27:31 CST 2005
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.
More information about the thelist
mailing list