CODEWITHSUNDEEP
sqlinner-join
look_up
look_up

Reputation: 71

How to define INNER JOIN in SQL

I have a problem how to define INNER JOIN in SQL. The problem is that I have a table Names with columns ID and Name. The other table has column PersonID.

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 PersonID.

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 Name from Names?

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.

Upvotes: 1

Views: 63

Answers (3)

xQbert
xQbert

Reputation: 35323

Alternative using a union all..

CREATE OR ALTER VIEW dbo.Test
AS
    SELECT 
        a.Column1,
        a.Column2,
        ...,
        b.Name as PersonName
    FROM dbo.SomeTableName AS a
    INNER JOIN Names on A.PERSONID = B.ID
    UNION ALL
    SELECT 
        a.Column1,
        a.Column2,
        ...,
        '' as PersonName
    FROM dbo.SomeTableName as A
    WHERE A.PersonID = 0

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

You should use LEFT JOIN instead. ie:

CREATE OR ALTER VIEW dbo.Test
AS
SELECT a.Colum1,
    a.Colum2,
    ...,
    Coalesce(b.Name,'') AS PersonName
FROM dbo.ExampleA AS a
LEFT JOIN dbo.Names AS b
    ON a.PersonId = b.Id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

It sounds like you want LEFT JOIN:

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 CASE.

Upvotes: 1

Related Questions