I have a problem how to define INNER JOIN in SQL. The problem is that I have a table
Names with columns
Name. The other table has column
Sometimes value in column
PersonID is 0, and in
Names table there is no row with a column value of 0.
I have created a view, and I am using an
INNER JOIN. Problem is that
SELECT statement in the view only works when there is value greater than zero in column
How can I define an
INNER JOIN when value is 0, so that column is NULL, and when is not zero then value should be
What I mean is:
CREATE OR ALTER VIEW dbo.Test AS SELECT a.Column1, a.Column2, ..., CASE WHEN a.PersonID IS NULL THEN '' ELSE b.Name END AS PersonName FROM dbo.ExampleA AS a INNER JOIN -- What to put in here that value is NULL when PersonID = 0, -- and when PersonID > 0 then join value should be a.PersonID = b.ID? dbo.Names AS b ON ???
Thank you in advance for any help.
It sounds like you want
SELECT e.Colum1, e.Colum2, . . . , COALESCE(e.name, n.Name) AS PersonName FROM dbo.ExampleA e LEFT JOIN dbo.Names n ON n.personid = e.personid;
Note that this also changes the table aliases to meaningful abbreviations for the tables. And uses
COALESCE() instead of