March 4, 2013 at 2:49 pm
Hey guys,
I have these result codes from one table...There are like 12 distinct ones...When I join to the another table to get how many accounts were associated with each result code, Im getting back the correct numbers that I am looking for...But the requestor has asked to show all the result codes even if there are 0 accounts associated with them...But for some reason I am just getting back the result code that has a number attached to it...If it is zero it just doesnt show up...I only am getting back like 6 rows where I should be getting back 12...Let me also say, I have to join to this other table to get a specific type
I tried putting all the distinct codes in a temp table, then selecting from the temp table and doing a left outer join on the other table to get the number accounts and inner join the other table to get the correct types but it still only returns the 6 codes that have data associated to them...
Any suggestions?
March 4, 2013 at 2:59 pm
asm1212 (3/4/2013)
Hey guys,I have these result codes from one table...There are like 12 distinct ones...When I join to the another table to get how many accounts were associated with each result code, Im getting back the correct numbers that I am looking for...But the requestor has asked to show all the result codes even if there are 0 accounts associated with them...But for some reason I am just getting back the result code that has a number attached to it...If it is zero it just doesnt show up...I only am getting back like 6 rows where I should be getting back 12...Let me also say, I have to join to this other table to get a specific type
I tried putting all the distinct codes in a temp table, then selecting from the temp table and doing a left outer join on the other table to get the number accounts and inner join the other table to get the correct types but it still only returns the 6 codes that have data associated to them...
Any suggestions?
Pretty sparse on details but it sounds like you are using an inner join. If you want all rows regardless of matching the second table you need to use a left join.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 2:59 pm
Here is example
First query to get resultcode and put in temp table:
SELECT DISTINCT rc.ResultCode
FROM dbo.ResultCodes rc
RESULTS:
ResultCode
CC
CO
E/O
IP
LM
NA
NP
NR
PX
SS
WN
THEN here is the query I am trying to run:
SELECT rc.ResultCode,
COUNT(a.AccountNumber)
FROM dbo.ResultCodes rc
LEFT OUTER JOIN dbo.Accounts a ON rc.ResultCode = a.ResultCode
INNER JOIN dbo.[Types] t ON a.AccountNumber = t.AccountNumber
WHERE t.[Type] = 'MHIC'
GROUP BY rc.ResultCode
And here is the results:
ResultCodeCount
CC 3
CO 1
LM 3
See I need all the result codes to show up with 0 even if no accounts are associated with them
March 4, 2013 at 3:14 pm
Well obviously your sample and your query don't quite match up. 😉
But you still have an inner join in here.
SELECT rc.ResultCode,
COUNT(a.AccountNumber)
FROM dbo.ResultCodes rc
LEFT OUTER JOIN dbo.Accounts a ON rc.ResultCode = a.ResultCode
INNER JOIN dbo.[Types] t ON a.AccountNumber = t.AccountNumber
WHERE t.[Type] = 'MHIC'
GROUP BY rc.ResultCode
This will ONLY return rows where there is a match on a.AccountNumber to t.AccountNumber. That means you will not get all ResultCodes. Change that to a left join.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 3:17 pm
You will have to move your where condition to the join also. I have to say that your tables look like they could use some normalization.
Something like this is maybe close.
SELECT rc.ResultCode,
COUNT(a.AccountNumber)
FROM ResultCodes rc
LEFT OUTER JOIN Accounts a ON rc.ResultCode = a.ResultCode
left JOIN [Types] t ON a.AccountNumber = t.AccountNumber and t.[Type] = 'MHIC'
GROUP BY rc.ResultCode
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 3:18 pm
Thank you sir...That seemed to have worked!
March 4, 2013 at 3:24 pm
Nevermind...Worked like a charm with the example I provided,
however when I applied to my original query, I am getting back more results than I was before...Like before the way I had it, the CC result code had 42 account numbers, but when I changed the INNER JOIN to LEFT JOIN, it came back with 58 account numbers and that is not correct...The 42 is what is expected...
Back to the drawing board again!
March 4, 2013 at 3:26 pm
Ok nevermind again...I was counting the wrong account number from a different table...I have it now and it is correct...Thank you for your input, it is greatly appreciated!
March 4, 2013 at 3:30 pm
asm1212 (3/4/2013)
Ok nevermind again...I was counting the wrong account number from a different table...I have it now and it is correct...Thank you for your input, it is greatly appreciated!
You are welcome. Glad you got it sorted out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 4:14 pm
Sean Lange (3/4/2013)
Something like this is maybe close.
SELECT rc.ResultCode,
COUNT(a.AccountNumber)
FROM ResultCodes rc
LEFT OUTER JOIN Accounts a ON rc.ResultCode = a.ResultCode
left JOIN [Types] t ON a.AccountNumber = t.AccountNumber and t.[Type] = 'MHIC'
GROUP BY rc.ResultCode
I don't htink it would be any different from this:
SELECT rc.ResultCode,
COUNT(a.AccountNumber)
FROM ResultCodes rc
LEFT OUTER JOIN Accounts a ON rc.ResultCode = a.ResultCode
-- left JOIN [Types] t ON a.AccountNumber = t.AccountNumber and t.[Type] = 'MHIC'
GROUP BY rc.ResultCode
Left Join eliminates any check against the account type.
If you need to count only account with a specific type you need to use something like this:
SELECT rc.ResultCode,
COUNT(a.AccountNumber)
FROM Accounts a
INNER JOIN [Types] t ON a.AccountNumber = t.AccountNumber and t.[Type] = 'MHIC'
RIGHT OUTER JOIN ResultCodes rc ON rc.ResultCode = a.ResultCode
GROUP BY rc.ResultCode
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply