Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: why not showing the nulls?

    Hi,

    I have two tbls joined via a varchar field.. which in tbl I have to use a CASE statement to format it correctly for the JOIN..

    All works well.. however, if I add a WHERE clause to the statement, with the WHERE clause affecting only one of the JOINED tbls.. All the NULLS are not shown.

    I should get results like:

    Postcode Count

    tf4 0
    tf5 23
    tf6 15
    tf7 0
    etc

    Whereas, when I add the where clause, none of the "zeros" are shown, so I get:

    tf5 23
    tf6 15

    Why is this?



    Here's the SQL:



    SELECT ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
    WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0)
    AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
    dbo.UkPostcodes.Master_Account
    FROM dbo.UkPostcodes LEFT OUTER JOIN
    dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
    WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
    ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
    WHERE (dbo.Customers_Trade.TradeCustomerID IN
    (SELECT DISTINCT TradeCustomerID
    FROM dbo.Orders_Trade
    WHERE (DATEDIFF(d, order_date, GETDATE()) < 60))) AND (dbo.Customers_Trade.is_activated <> 'No') OR
    (dbo.Customers_Trade.BillingAccountID <> '') AND (dbo.Customers_Trade.BillingAccountID IN
    (SELECT DISTINCT TradeCustomerID
    FROM dbo.Orders_Trade
    WHERE (DATEDIFF(d, order_date, GETDATE()) < 60) AND (is_activated <> 'No')))
    GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
    dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account





    does not show the nulls:



    where as:



    SELECT ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
    WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0)
    AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
    dbo.UkPostcodes.Master_Account
    FROM dbo.UkPostcodes LEFT OUTER JOIN
    dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
    WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
    ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
    GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
    dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mtm81
    Why is this?
    because conditions on the right table in a LEFT OUTER JOIN should be in the ON clause, not the WHERE clause
    Code:
    SELECT ISNULL(COUNT(CT.PostCode),0)  AS PCCount
         , P.Postcode
         , P.Town
         , P.County
         , P.Area
         , P.Country
         , P.Master_Account
      FROM dbo.UkPostcodes as P
    LEFT OUTER 
      JOIN dbo.Customers_Trade as CT
        ON P.Postcode = 
              CASE LEN(REPLACE(CT.PostCode,' ','')) 
              WHEN 5 
              THEN LEFT(REPLACE(CT.PostCode,' ',''),2) 
              WHEN 6 
              THEN LEFT(REPLACE(CT.PostCode,' ',''),3) 
              ELSE LEFT(REPLACE(CT.PostCode,' ',''),4) END
       AND CT.TradeCustomerID IN
            ( SELECT DISTINCT TradeCustomerID
                FROM dbo.Orders_Trade
               WHERE DATEDIFF(d,order_date,GETDATE()) < 60 )  
       AND CT.is_activated <> 'No' 
        OR CT.BillingAccountID <> '' 
       AND CT.BillingAccountID IN
            ( SELECT DISTINCT TradeCustomerID
                FROM dbo.Orders_Trade
               WHERE DATEDIFF(d,order_date,GETDATE()) < 60
                 AND is_activated <> 'No' )
    GROUP 
        BY P.Postcode
         , P.Town
         , P.County
         , P.Area
         , P.Country
         , P.Master_Account
    i think you should have a closer look at your ANDs and ORs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    3
    Hi, Many thanks for your reply!
    I inputted your amended query and it returned an odd result - it was giving that Count value as 70 for every record!

    Perhaps if I explain what I'm trying to do it may help?
    I have three tbls:
    UkPostcodes
    Tradecustomers
    Orders_trade

    The ukpostcodes tbl contains all the district postcodes (between 2-to four characters)..
    I need to match these to the postcodes for the tradecustomers and get a count..

    So I can see how many trade customers there are in any postcode district.


    All pretty straight forward.

    However.. the trade customers need to be filtered by the last date they ordered.

    So I need to check if either the tradecustomer's TradeCustomerID AND/OR their BillingAccountID (this is a value they have if they are a sub-office of a main tradecustomer) has had an order in the last "x" amount of days.

    does that make sense?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mtm81
    does that make sense?
    no

    especially the AND/OR part



    like i said, you should re-examine your ANDs and ORs

    i trust my reformatting and simplification of your query will make this task easier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •