CODEWITHSUNDEEP
sqlsql-serverjoininner-join
user1777929
user1777929

Reputation: 797

How to join a SQL Server table that has Null Values?

I wrote code to pull up how many Licence plates are registered to a particular Account number. It also gives the make of that vehicle.

I'm using 4 tables to get this info:

  1. Account table
  2. Plate table
  3. VehClass table (to give a description of type of vehicle. Eg; Cars, Truck, etc)
  4. VehicleMake table (fields are VehicleMakesID and VehicleMakeDesc

Problem is, the Plate table. A lot of entries have NULL in the VehicleMakeID field. Hence can't join to VehicleMake table to get the description of vehicle.

My code is as follows:

select ac.AccountNumber
    , p.LicPLateno
    , p.LicPlateState
    , p.LicPlateCountry
    , vm.VehicleMakeDesc
    , p.VehicleModel
    , p.VehicleYear
    , v.VehShortDesc
    --, ISNULL(p.VehicleMakeId,'-1') VechicleMakeId  --(-1 means Other)
    , p.VehicleMakeId

from account ac 
inner join Plate p on ac.AccountId=p.AccountId 
inner join VehClass v on p.VehClassId=v.VehClassID 
inner join VehicleMake vm on p.VehicleMakeId=vm.VehicleMakeId

where ac.AccountNumber= '12345678'
  and p.PlateStatusId=1  --(For Active Plates only)
  and p.EndDate is null  --(Plates are not expired)

order by p.LicPlateNo

The results I get are: 

ABS123  BC  CA  Other   - NULL  Cars    -1  
DEF345  BC  CA  Other   - NULL  Cars    -1  
GHI456  BC  CA  Other   - NULL  Cars    -1  
HIJ567  BC  CA  Other   - NULL  Cars    -1

The results show only 4 plates but there are actually 31 plates. Those other plates are not showing up because those other plates have NULL in the VehicleMakeID field. Hence, they are not joining with the VehicleMake table.

How do I get a list of ALL the 31 plates where if the VehicleMakeID is NULL then the Description should show as Other?

Upvotes: 1

Views: 81

Answers (1)

Lamak
Lamak

Reputation: 70638

Change the INNER JOIN with the VehicleMake table for a LEFT JOIN:

select ac.AccountNumber
    , p.LicPLateno
    , p.LicPlateState
    , p.LicPlateCountry
    , ISNULL(vm.VehicleMakeDesc,'Other')
    , p.VehicleModel
    , p.VehicleYear
    , v.VehShortDesc
    , ISNULL(p.VehicleMakeId,'-1') VechicleMakeId  --(-1 means Other)
    --, p.VehicleMakeId

from account ac 
inner join Plate p on ac.AccountId=p.AccountId 
inner join VehClass v on p.VehClassId=v.VehClassID 
left join VehicleMake vm on p.VehicleMakeId=vm.VehicleMakeId

where ac.AccountNumber= '12345678'
  and p.PlateStatusId=1  --(For Active Plates only)
  and p.EndDate is null  --(Plates are not expired)

order by p.LicPlateNo
;

Upvotes: 2

Related Questions