SQL - Compare one column to some column to find match value-CodePudding

I have some dataset and I will compare it with self join

The thing is, I want to compare Column A with Column B, C, and D. If column A has no match with all of the three column, then I will insert it to table.

Otherwise, if column A has matching value with at least one of column B, C, or D then I will not insert the value.

Here is my example data

ID     ColA     ColB     ColC     ColD
1      ORANGE   RED      BLUE     GREEN
2      RED      YELLOW   RED      PURPLE

Here is my expected result. Since ID 1 has not match value, then it will be inserted, and ID 2 will not be inserted

ID     ColA     ColB     ColC     ColD
1      ORANGE   RED      BLUE     GREEN

I tried LEFT JOIN but ID 2 still in the result

select a.*
from TableA a
left join TableA b
on a.ID = b.ID
where 1=1
and (a.ColA <> b.ColB
  OR a.ColA <> b.ColC
  OR a.ColA <> b.ColD)

Is there something I miss?

Thanks

UPDATE

Thanks @Dale K for helping me, right now I add some dataset regarding our comments.

If there are NULL values on ColB, ColC or ColD How do I get the expected results with data below

ID     ColA     ColB     ColC     ColD
3      YELLOW   NULL     YELLOW   RED   
4      RED      NULL     NULL     RED
5      GREEN    NULL     BLUE     RED

Since only ID 5 has no match value with ColA, here is the expected result

ID     ColA     ColB     ColC     ColD
5      GREEN    NULL     BLUE     RED

Thanks

CodePudding user response：

You don't even need a join, and you want AND not OR i.e. you want all the conditions to be true, not just one of them.

And assuming ColA can never be null or blank gives

select a.*
from TableA
where ColA <> coalesce(ColB,'') and ColA <> coalesce(ColC,'') and ColA <> coalesce(ColD,'');

This will however match if ColB & ColC & ColD are all null.

CodePudding user response：

It should be

    select *
    from TableA 
    where  (ColA <> ColB
    AND ColA <> ColC
    AND ColA <> ColD)

CodePudding user response：

If you want to deal with nulls correctly (null equals null but not blank '') then you need the following

SELECT a.*
FROM TableA a
WHERE NOT EXISTS (
  SELECT a.ColA
  INTERSECT (
    SELECT a.ColB
    UNION ALL
    SELECT a.ColC
    UNION ALL
    SELECT a.ColD
  )
);