February 23, 2009 at 8:30 am
Hello,
I have the query below which will return Col1-Col3 along with the number of occurences of each value of Col2 occurs. The problem is If there were no occurences (NULL value for Col2 for a particular record), I still want Col1-Col3 to be shown and I want the number of occurences to be shown as 0. I thought simply using LEFT JOIN's would do this but apparently not. Anybody have an idea of how this is accomplished?
Thanks
SELECT Col1, Col2, Col3,
COUNT(Col2) AS Number of occurences
FROM TableA
LEFT JOIN TableB
ON TableB.Col4 = TableA.Col4
LEFT JOIN TableC
ON TableC.Col5 = TableB.Col5
GROUP BY Col1, Col2, Col3;
February 23, 2009 at 8:42 am
Try using ISNULL(COUNT(col2), 0). That should replace the NULL values with a zero.
Greg
February 23, 2009 at 9:31 am
Well the problem is that i'm not getting any return from any records that don't have a value in all of the columns. If a record has a value in col1 but no values in col2 and col3, then that record is not even included in the return set. It's not that it shows up as NULL in the return set, it doesn't show up at all. I thought that was the purpose of LEFT JOIN's so i'm confused as to why it's functioning this way.
Thanks for your reply btw.
February 23, 2009 at 9:43 am
Is that the ACTUAL query you're using? Which tables are col1, col2 and col3 from?
something isn't right in here. Also - if you just want to count occurrences, then do a count(*) since it will count without care for nullability.
If that's not the real query - then post the real query, because I suspect something isn't the same between it and your example query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2009 at 9:55 am
I agree with Matt. The pseudo code that you posted doesn't tell us what tables the three columns are from or what the relationships between the tables are, etc. Can you post table schema and sample data?
Greg
February 23, 2009 at 10:20 am
No it's not the actual code. I just substituted the names. I actually figured it out and it's working now. Apparently I needed to change the table order in my JOIN. Instead of selecting from tableA and LEFT JOIN tableB, I changed it to select from tableB and LEFT JOIN tableA and it works properly now. Sorry for the confusion and thanks for you help.
February 23, 2009 at 11:08 am
FULL OUTER JOIN would solve it all.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply