Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ianwooly0
Starting Member
8 Posts |
 Posted - 2015-01-19 : 08:25:28
|
| Hi,I have a statement below which is returning some data from one table and then a count for corresponding rows in another table. I get 5 results as I expect, 3 of them have a significant number in the count column but the other 2 are returning 1 as the count. These should be 0! Query is below, any ideas? Thanks |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-01-19 : 08:59:06
|
| post the data you're working with. |
 |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2015-01-19 : 09:16:37
|
Your data is useless as it does not show your problem and is not consumable. (ie CREATE TABLE .. INSERT.. etc)You are counting all the result set, not just table b.Your first query should probably be something like:SELECT a.Id, a.Name, COUNT (b.School_Type_Id) AS TotalFROM School_Type a LEFT JOIN School_To_School_Type b ON a.Id = b.School_Type_IdGROUP BY a.Id, a.Name; |
|
|
|
ianwooly0
Starting Member
8 Posts |
Posted - 2015-01-19 : 09:27:44
|
| Thank you Ifor that has worked. My data couldnt have been that bad if you supplied the correct answer. 10/10 for sql knowledge, 3/10 for people skills. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-01-19 : 09:58:50
|
| @ianwooly0 your sample data is not useful because if you run your query against your sample data the problem does not occur. Ifor simply meant (as did I) post sample data that demonstrates the problem. Sorry if I was not specific.FWIW "consumable" means that you should post CREATE TABLE statements for each table and INSERT INTO statements to populate the tables with sample data that demonstrates the problem. |
|
|
|
|
|
|
Count result showing as 1 instead of 0