joining on a NULL value? 1

[BlackDice]

I have an SQL statement that selects from a number of tables using left joins. But it's joining a table on a .NULL. value! Here's an example:

select firstname,lastname from tblpeople ;
left join tblVehicle on tblVehicle.vehiclepk = tblpeople.vehiclefk ;
left join tblInsurance on tblInsurance.Insurancepk = tblVehicle.insurancefk

if the people record has no vehicle record linked to it, then tblVehicle.vehiclepk is null, as well as tblVehicle.Insurancefk. But it's joining a '1' from tblInsurance.insurancepk on the .NULL. value resulting from tblVehicle.insurancefk. How can a .NULL. be joined on a 1? Does anyone know a way around this? Why is this even happening?

BlackDice

http://www.stillwaterexpress.com

 

[Stella740pl]

Does anyone know a way around this?

Depends on what you want to achieve. Do you want to include all people or only those with insurance (or what's that)? Do you want to select 0 or space instead of NULL? Take a look at INNER JOIN clause. Check out NVL() function. Anything close to what you want?

 

[BlackDice]

well, I still want the person record, even if there's no vehicle or insurance record. but if I choose to do this:

select firstname,lastname,tblVehicle.type,tblInsurance.Company from tblpeople ;
left join tblVehicle on tblVehicle.vehiclepk = tblpeople.vehiclefk ;
left join tblInsurance on tblInsurance.Insurancepk = tblVehicle.insurancefk

It's returning this:

firstname     lastname     type     company
JOHN          SMITH                 AMERICAN FAMILY

even though it should be returning this:

firstname     lastname     type     company
JOHN          SMITH

because tblVehicle.insurancefk = .NULL and tblInsurance.insurancepk = 1, but it's still joining a 1 on a .NULL.!!


BlackDice

http://www.stillwaterexpress.com

 

[Stella740pl]

What type insurancefk field?

 

[Stella740pl]

If insurancefk is character, you should try joining on

tblInsurance.Insurancepk == tblVehicle.insurancefk

In any case, you should also try using inner join for the second join. You don't need listing all insurance companies in your cursor when there is no record of vehicle or insurance found.

 

[BlackDice]

they're both integer fields, I just put quotes around the '1' for emphasis (sorry). I got it working now. I left the joins the same, I just reordered them, and now it works! don't know why, but it does

BlackDice

http://www.stillwaterexpress.com

 

[Stella740pl]

You mean, you put a second join first and the other way around? I guess it should make a difference. Even though I can't recall having problems because of that, I probably had them one time or another.

Glad it works now.

 

[BlackDice]

Thanks for your help

BlackDice

http://www.stillwaterexpress.com