CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Jesus Hamdi
Jesus Hamdi

Reputation: 43

Find data not exists in another table based on a column

In SQL Server 2016, I have those two tables:

Table A:

ID   Description
X      Desc1
Y      Desc2
Z      Desc3

Table B:

   Name  F_ID   F_Description
    A     X      Desc1
    A     Y      Desc2
    B     X      Desc1
    C     Y      Desc2
    C     Z      Desc3
    D     Z      Desc3

I should return all data with an IfExist column (bool) column.

My Expected output is:

   Name  F_ID   F_Description   IfExist
    A     X      Desc1             1
    A     Y      Desc2             1
    A     Z      Desc3             0
    B     X      Desc1             1
    B     Y      Desc2             0
    B     Z      Desc3             0
    C     X      Desc1             0
    C     Y      Desc2             1
    C     Z      Desc3             1
    D     X      Desc1             0
    D     Y      Desc2             0
    D     Z      Desc3             1

How can I manage it?

Upvotes: 1

Views: 27

Answers (1)

kjmerf
kjmerf

Reputation: 4345

I think you need:

SELECT b.Name
    , a.F_ID
    , a.F_Description
    , CASE WHEN b2.ID IS NULL THEN 0 ELSE 1 END AS IfExists
FROM (
    SELECT DISTINCT Name
    FROM tableB
    ) b
CROSS JOIN tableA a
LEFT JOIN tableB b2 ON b.Name = b2.Name AND a.ID = b2.F_ID

Upvotes: 1

Related Questions