asked on

Need help pulling data will NULL fields from MS SQL query.

Hello,
I have a query which pulls contact data from accounts that a salesman has and also includes sales and profit data from the prior year. This works fine, except if the account had no activity in 2012 (or no entry for 2012 in the arcsales table) then it doesn't pull any data for that account. I need it to pull the contact info regardless if there is Sales and Profit info. I've been told I need to check for null fields for T3.YTDTotSales and
T3.YTDPubProfit but I am ubsure how to do this.

SELECT
  T.CustAcct,
  T.CustomerName,
T.Address1,
T.Address2,
  T.City,
  T.State,
  T.Zip,T3.YTDTotSales,
  T3.YTDPubProfit
  T1.FirstName As SalesContactFirstName,
  T1.LastName As SalesContactLastName,
T1.EmailAddress,
  T2.SmanCode As SmanCode,

FROM arcusts T
  Left Outer Join arcconts T1 On T.SalesContactID = T1.CustContactID
  Left Outer Join arsmen T2 On T.SmanID = T2.SmanID
  Left Outer Join arcsales T3 On T.CustID = T3.CustID
WHERE T3.FiscalYear=2012
and T2.SmanCode='Salesman1'

Open in new window

Sean Stuber

putting the conditions in the where clause effectively turns your outer joins into inner joins

try keeping them in the join clause

SELECT
T.CustAcct,
T.CustomerName,
T.Address1,
T.Address2,
T.City,
T.State,
T.Zip,T3.YTDTotSales,
T3.YTDPubProfit
T1.FirstName As SalesContactFirstName,
T1.LastName As SalesContactLastName,
T1.EmailAddress,
T2.SmanCode As SmanCode,

FROM arcusts T
Left Outer Join arcconts T1 On T.SalesContactID = T1.CustContactID
Left Outer Join arsmen T2 On T.SmanID = T2.SmanID
Left Outer Join arcsales T3 On T.CustID = T3.CustID
and T3.FiscalYear=2012
and T2.SmanCode='Salesman1'

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial