I have the following sql statement:
SELECT Useraccount.Name, Company.Name FROM Useraccount, Company WHERE Useraccount.CompanyId = Company.ID This statement shows all useraccount-names and the name of their company.
But I also have useraccounts where the companyId is 0 and I'd like to get them displayed too (Name of the company could be an empty string in this case). How can I do that?
Use LEFT JOIN instead:
SELECT Useraccount.Name, Company.Name FROM Useraccount LEFT JOIN Company ON Useraccount.CompanyId = Company.ID; You were INNER JOINing the two tables with the old JOIN syntax. Please avoid this old join syntax and use the ANSI SQL-92 explicit syntax using the JOIN keyword. See this for more information:
For more info about the different join types:
A LEFT JOIN would do the trick for you:
SELECT Useraccount.Name, Company.Name FROM Useraccount LEFT JOIN Company ON Useraccount.CompanyId = Company.ID You can use -
SELECT Useraccount.Name, Company.Name FROM Useraccount, Company WHERE Useraccount.CompanyId = Company.ID or Useraccount.CompanyId =0 When you use LEFT JOIN, you will get all rows from left table irrespective of the matching status of rows.
SELECT Useraccount.Name AS UserAccountName, Company.Name AS CompanyName FROM Useraccount LEFT JOIN Company ON Useraccount.CompanyId = Company.ID; 
0. I don't like them but that's whatNULLis for. You can add an explicit foreign key constraint if you convert the 0s to Nulls.