How to write a T-Sql query that would output 1 if the value in a column in Table A matches the value in a column in Table B, 0 otherwise?

Home / Uncategorized / How to write a T-Sql query that would output 1 if the value in a column in Table A matches the value in a column in Table B, 0 otherwise?

Question:
I am using SQL Server 2012 and I need to write a query that would do give me the following output:Code Value
FRBAR 0
ENSPA 1
DEWINE 1

To achieve the above, I am working with 2 specific tables in my database, namely Table A and Table B. Table A has a column called CodeA and Table B has a column called CodeB.

I want to write a T-sql query that would match all the values in column CodeA with those in column CodeB and ouput the result I mentioned above.

I know I need to do a JOIN on these 2 columns but I am confused about how to implement the logic of returning a 1 if there is a match and a 0 otherwise.


Answer:
You need to use a FULL OUTER JOIN:SELECT CASE
WHEN CodeA IS NOT NULL THEN CodeA
ELSE CodeB
END AS Code,
CASE
WHEN CodeA IS NOT NULL AND CodeB IS NOT NULL THEN 0
ELSE 1
END AS Value
FROM TableA
FULL OUTER JOIN TableB ON CodeA = CodeB

If there is a match the query will output 1 as Value. Otherwise the query will output 0 along with the not null code value.

Note: Using a LEFT JOIN will check only in one direction, i.e. only for codes in one table that don’t exist in the other table. If you want to check in both directions, then you have to use a FULL JOIN.
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *