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:
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: