How to use Join as a Select column

Question

I have the following query:

SELECT X.ID, X.NAME, CASE R.ID WHEN NOT NULL THEN 1 ELSE 0 END ISRELATED
FROM TABLE X
INNER JOIN RELATIVE R ON R.NAMEID = X.ID

I'm trying to get results look like this structure, and not pull any duplicate rows from table X:

ID
NAME
ISRELATED (boolean)

Any suggestions? Thanks.

Answer 1

I think that you has an error in CASE and have to use LEFT JOIN

SELECT DISTINCT
    X.ID, 
    X.NAME, 
    CASE WHEN R.ID IS NOT NULL THEN 1 ELSE 0 END ISRELATED 
FROM TABLE X 
LEFT JOIN RELATIVE R ON R.NAMEID = X.ID 

Answer 2

Using INNER JOIN will return only those rows from X which have a corresponding row in R. Use Left outer join instead.

SELECT DISTINCT X.ID, X.NAME, CASE WHEN R.ID IS NOT NULL THEN 1 ELSE 0 END ISRELATED 
FROM TABLE X 
LEFT OUTER JOIN RELATIVE R ON R.NAMEID = X.ID 

Answer 3

try this

SELECT DISTINCT X.ID, X.NAME, CASE WHEN R.ID IS NOT NULL THEN 1 ELSE 0 END ISRELATED 
FROM TABLE X 
INNER JOIN RELATIVE R ON R.NAMEID = X.ID