Search code examples
sqlsql-serversubqueryleft-join

SQL Server procedure - Merge two tables without duplicates - if table B column B =1 then

I am trying to Merge two tables in SQL with no duplicates. I need the values of Table B column B to be selected if the values=1. Table A is a list box containing the default values and table B holds the list box values per user.

Table A

Col_A  Col_B
------------
701     null
702     null
703     null
704     null

Table B

Col_A  Col_B
-------------
701    1
703    1

Desired result

Col_A  Col_B
-------------
701    1
702    null
703    1
704    null
Solution

  • Do you want a left join?

    select a.col_a, b.col_b
from table_a a
left join table_b b on b.col_a = a.col_a

    I need the values of Table B column B to be selected if the values=1

    In your sample data, all rows in table b have value 1. If you do want to filter on the value as well, then add another condition to the left join:

    select a.col_a, b.col_b
from table_a a
left join table_b b on b.col_a = a.col_a and b.col_b = 1

    With these two columns only, this does not seem very helful though. If you just want to know if some row exists in table b with the same col_a and a value of 1, then exists is more appropriate:

    select a.col_a,
    case when exists (select 1 from tableb b where b.col_a = a.col_a and b.col_b = 1)
        then 1
    end as col_b
from table_a a