Query - Joint issue

[Number11]

So i have a query that is looking up in two tables i have it set to join on the

([Order Master].[Job Type] = [WarehouseDatabase Order].[Job Type])
AND
([Order Master].[Appointment Type] = [WarehouseDatabase Order].[Appointment Type]) AND
([Order Master].[Time Slot] = [WarehouseDatabase Order].[Time Slot])
AND ([Order Master].[Appointment Date] = [WarehouseDatabase Order].[Appointment Date])
AND
([Order Master].PRODUCTID = [WarehouseDatabase Order].PRODUCTID)
AND
([Order Master].STATUS = [WarehouseDatabase Order]. STATUS)

However if the Status is blank within the Order Master its not pulling through anything from WarehouseDatabase, so i need to set this to if field is blank ignore join???

 

[arnelgp]

use Left Join:

... From [Order Master] Left Join [WarehouseDatabase Order] Where
([Order Master].[Job Type] = [WarehouseDatabase Order].[Job Type])
AND
([Order Master].[Appointment Type] = [WarehouseDatabase Order].[Appointment Type]) AND
([Order Master].[Time Slot] = [WarehouseDatabase Order].[Time Slot])
AND ([Order Master].[Appointment Date] = [WarehouseDatabase Order].[Appointment Date])
AND
([Order Master].PRODUCTID = [WarehouseDatabase Order].PRODUCTID)
AND
([Order Master].STATUS = [WarehouseDatabase Order]. STATUS)

 

[plog]

That's a lot of fields to JOIN on. Why do you have 2 tables with so many common fields? I fear you have a poorly structured database.

 

[pbaldy]

From [Order Master] Left Join [WarehouseDatabase Order] Where

Maybe ON instead of WHERE.