Query - Joint issue (1 Viewer)

Number11

Member
Local time
Today, 23:24
Joined
Jan 29, 2020
Messages
607
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:24
Joined
May 7, 2009
Messages
19,169
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

Banishment Pending
Local time
Today, 18:24
Joined
May 11, 2011
Messages
11,613
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.
 

Users who are viewing this thread

Top Bottom