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
-
i recommend a good T sql book or training course, or even a read through BOL on joins and nulls.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
colin,
pushed for time - just need an answer...
-
Darth,
I suppose this
“WHERE (dbo.Customers_Trade.TradeCustomerID IN..............."
is restricting from the rows returned..
Modify the query so that it should look like..
“AND (dbo.Customers_Trade.TradeCustomerID IN..............."
--Ramesh
-
Thanks for the reply, but I'm not sure that is correct :
My query now looks like:
SELECT ISNULL(COUNT(dbo.Customers_Trade.TradeCustomerID), 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 AND 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
That means there isn't a WHERE clause.. and what was the WHERE clause is now JOINED to the JOIN statement with an AND part..
So when I run the query I just get a timeout..
-
Darth,
Let’s have a look closely at the WHERE Clause..
I suppose you have an OR operator which is mis-braced or its an AND operation. If the operation is infact an OR then it should be braced as such as given below. The OR operation which returns true if any of the other logical operations are true as the OR is not grouped, because of which the query time-outs.
Looking at the query, one can conclude that this query will surely hamper the performance for larger tables because
· It scans the same table (dbo.Orders_Trade) twice.
· Scalar operations on columns DATEDIFF( d, order_date, GETDATE() ) will result in table scans.
· Lot of case statements which would surely scan the whole tables.
You can easily optimize the query using derived tables.
------------------------------------
--Original version
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 ---------Need a check here
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'
)
)
------------------------------------
---See modified version
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 ---------check here
(
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'
)
)
)
--Ramesh
-
Hi - thanks for the reply!
ok first things first.. when I put that modified where statement into the query I get an Error:
"INcorrect Syntax near the word AND"
I'm guessing it's just a misplaced ) somewhere but my eyes can't see it.
Secondly- just to explain what I'm doing with the query.
The UK Postcodes tbl holds all uk postcode districts (these are the first 2-4 digits in the postcode)
I want to show how many tradecustomers there are in any one district (including those with none)
The WHERE clause simply looks in the trade orders tbl to see when the last time the trade customer ordered so I can filter the trade customers who haven't ordered in a few months and so are not needed
Finally, any one tradecustomer can be either a main account (TradeCustomerID) or a sub-account (BillingAccountID) or both in some cases.. this is why I need to check the orders tbl based on either the TradeCustomerID value or the BillingAccountID ..
hope that makes sense...
-
Here's the statement which gives the error:
SELECT ISNULL(COUNT(dbo.Customers_Trade.TradeCustomerID), 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
-
Darth,
Got a hint of solution......
rewriting the query .....Let me know if this has helped you or not....
SELECT ISNULL(COUNT(dbo.Customers_Trade.TradeCustomerID), 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
INNER JOIN ( SELECT DISTINCT TradeCustomerID FROM dbo.Orders_Trade
WHERE DATEDIFF(d, order_date, GETDATE()) < 60
) Orders_Trade
ON ( dbo.Customers_Trade.TradeCustomerID = Orders_Trade.TradeCustomerID OR
dbo.Customers_Trade.BillingAccountID = Orders_Trade.TradeCustomerID
) AND dbo.Customers_Trade.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
--Ramesh
-
Hi again! - thanks once again for the assistance.
No joy I'm afraid! - brings in results.. but no nulls..
-
Here goes, two feet first straight in to the unknown!!!Try using a derived table subquery like this:
SELECT ISNULL(COUNT(derivedCustomers_Trade.TradeCustomerID), 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 (
SELECT dbo.Customers_Trade.TradeCustomerID,
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 as PostCode
FROM dbo.Customers_Trade
INNER JOIN ( SELECT DISTINCT TradeCustomerID FROM dbo.Orders_Trade
WHERE DATEDIFF(d, order_date, GETDATE()) < 60
) Orders_Trade
ON ( dbo.Customers_Trade.TradeCustomerID = Orders_Trade.TradeCustomerID OR
dbo.Customers_Trade.BillingAccountID = Orders_Trade.TradeCustomerID
) AND dbo.Customers_Trade.is_activated != 'No'
)derivedCustomers_Trade
ON dbo.UkPostcodes.Postcode = derivedCustomers_Trade
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
Good Luck!!
-
Hi, Thanks for the reply.
When I use that query I get an error about dbo. Customers_Trade not matching with a table name or alias used in the query!
-
Hi (again!)
Looking at that query - I don't think that would get me my results.
I know I need to list all of the UK postcode districts from the UKPOstcodes TBl. Added to that I need to display a COUNT of all of the TradeCustomerID's that have that postcode district from the Customers_Trade tbl.
I also need to format the Customers_Trade.Postcode as it has the full postcode not just the district so I need to use a select statement to get the match between it and the postcode district in the UKPostcodes tbl.
I have all of that working... however on top of all of that:
With the original query I am selecting ALL the tradecustomerID's in the Customers_Trade tbl. However what I actually need is to only select TradeCustomerID's who meet two criteria 1) - They have a value of <> 'No' as one of the other columns in the Customers_Trade tbl and also... they have ordered within the last 90 days - something I can check from the Orders_Trade tbl.
I have THIS query working also.. so I thought I would just do the JOIN on the Customers_Trade to UKPostcode and then add the WHERE statement - but it's not working..
If I move the WHERE statement to be part of the JOIN I just get a timeout...
Arrgh
-
Darth,
seems like still looking for a soln...
to be left in the middle of the conLooking at my query, I notice that i mistakenly used an INNER JOIN in place of a LEFT JOIN...
It makes life a difficult thing when you don't have the inputs and you want to give outputs....
--Ramesh
-
You say you have a working query which you then want to JOIN another table and add a WHERE clause... Could you store the results of the initial query in to a temporary table first, then you can query that with your JOIN and WHERE added in?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply