I am using SQL Server 2012 and I need to write a query that would do give me the following output:Code Value
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.
You need to use a FULL OUTER JOIN:SELECT CASE
WHEN CodeA IS NOT NULL THEN CodeA
END AS Code,
WHEN CodeA IS NOT NULL AND CodeB IS NOT NULL THEN 0
END AS Value
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.