June 12, 2015 at 9:02 am
Table Clients is master table with all records, joining against Assets table that may or may not have a matching entry.
Trying to sum an asset type against table and no matter what kind of join I do I cannot get SQL to return a NULL match against the Clients ID value. All I get back are matching rows.
Here's the SQL:
select c.cindex,SUM(a.value) AS 'Total Assets' from Clients c
"the join" Assets2012 a on a.clientid=c.cindex
where (c.ClientClass<=7 AND c.ClientClass<>6) AND a.assettype = 2
group by c.cindex
But no matter what type of join I do, left, right, left outer, inner, I am not getting back NULL values for client records that have no matching asset records.
Strange thing is, by removing the "and assettype=2" part I get the whole database back, with NULL's but not the range I'm looking for.
June 12, 2015 at 9:18 am
Gave up group by and just did this:
select c.cindex,(select SUM(balance) FROM Assets2013 a
WHERE a.ClientID = c.cindex AND a.assettype=2) from Clients c
where (c.ClientClass<=7 AND c.ClientClass<>6)
it's slower but it worked.
June 12, 2015 at 9:31 am
In general, DDL, sample data, and expected results would help us give you a solution that does what you want (check this link http://www.sqlservercentral.com/articles/Best+Practices/61537/).
Having said that, as you found out, your predicate on assettype is why you won't get any results for clients that don't have any corresponding assets.
The JOIN is processed first, and the assettype=2 is then applied, which is why you only end up with matching rows then.
Instead of the subquery version, you could just add assettype=2 to the JOIN condition, like this:
SELECT c.cindex,SUM(a.value) AS 'Total Assets' from Clients c
LEFT JOIN Assets2012 a on a.clientid=c.cindex AND a.assettype = 2
WHERE (c.ClientClass<=7 AND c.ClientClass<>6)
GROUP BY c.cindex
Depending on your data, that could end up being more efficient.
Cheers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply