SQLTeam.com | Weblogs | Forums

Why is the FULL JOIN not returning all the members of Customers table with no Orders

SELECT
tblOrders.PK_RefNo
,tblOrders.DateInstructed
,tblOrders.Address1
,tblOrders.Surname
,tblOrders.BookingType
,tblOrders.SourceID

FROM
tblCustomers

FULL OUTER JOIN tblOrders ON tblOrders.SourceID = tblCustomers.ProspectID

WHERE (tblOrders.DateInstructed >= @BOM AND tblOrders.DateInstructed <= @EOM AND
SourceID IN (@Source) AND tblCustomers.AgentDate > @AgentAdded)

Because of the WHERE conditions on tblOrders. I can't tell for sure which table SourceID is in.
Btw, that IN condition will not work as you expect it to, @Source will be treated as a single value, not a list of values.

SELECT
tblOrders.PK_RefNo
,tblOrders.DateInstructed
,tblOrders.Address1
,tblOrders.Surname
,tblOrders.BookingType
,tblOrders.SourceID

FROM
tblCustomers

FULL OUTER JOIN tblOrders ON tblOrders.SourceID = tblCustomers.ProspectID AND
    tblOrders.DateInstructed >= @BOM AND tblOrders.DateInstructed <= @EOM AND
    SourceID IN (@Source)

WHERE  tblCustomers.AgentDate > @AgentAdded