I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero.
For this I've tried this query:
Create table Streets(
ID int IDENTITY primary key,
Name varchar(100)
);
create table users(
ID int IDENTITY primary key,
Username varchar(100),
StreetID int references Streets(id)
);
insert into streets values ('1st street'), ('2nd street'), ('3rd street'),
('4th street'), ('5th street');
insert into users values ('Pol', 1), ('Doortje', 1), ('Marc', 2), ('Bieke', 2),
('Paulien', 2), ('Fernand', 2), ('Pascal', 2), ('Boma', 3),
('Goedele', 3), ('Xavier', 4);
select s.name as street, count(s.name) as count
from users u inner join streets s on u.streetid = s.id
group by s.name
And it gives me this output:
| | street | count |
| - | ---------- | ----- |
| 1 | 1st street | 2 |
| 2 | 2nd street | 5 |
| 3 | 3rd street | 2 |
| 4 | 4th street | 1 |
The problem is that the 5th street, where no user lives, doesn't appear on the result. Could I do this with SQL server? Here you've got a fiddle
Update: If I do right join
, I've got this result:
| | street | count |
| - | ---------- | ----- |
| 1 | 1st street | 2 |
| 2 | 2nd street | 5 |
| 3 | 3rd street | 2 |
| 4 | 4th street | 1 |
| 5 | 5th street | 1 |
COUNT(u.streetid)
– dnoeth May 26 '17 at 18:00right join
andright outer join
are same things. I did add an explanation in my answer as suggested by @jpmc26 . – SqlWorldWide May 30 '17 at 19:42