CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tech Learner
Tech Learner

Reputation: 1317

SQL Query to assign value to temp column based on condition

I am having Table1 with Column A and Table2 with Column B.

When I use join, (Table1.A = Table2.B) I need to create a temp column 'Flag' and set Flag value as '1' for matching records and for remaining records(Table1.A != Table2.B) should have flag value '0'.

Result set should have column from both the table and flag value.

Thanks in advance.

Upvotes: 0

Views: 2446

Answers (3)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with FULL OUTER JOIN .

SELECT *,CASE WHEN t1.A = t2.B THEN 1 ELSE 0 END Flag
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.A=t2.B

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

You can use LEFT JOIN with ISNULL as below:

SELECT *,CASE WHEN t2.B IS NULL THEN 0 ELSE 1 END Flag
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.A=t2.B

For displaying unmatched values also from right table

SELECT *,CASE WHEN t1.A IS NULL OR t2.B IS NULL THEN 0 ELSE 1 END Flag
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.A=t2.B

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Check below example:

DECLARE @tblA AS TABLE
(
    ID INT,
    A VARCHAR(50)
)

DECLARE @tblB AS TABLE
(
    ID INT,
    B VARCHAR(50)
)

INSERT INTO @tblA VALUES(1,'AA'),
(2,'CCC'),
(3,'DDD'),
(4,'FFF')

INSERT INTO @tblB VALUES(1,'AA'),
(2,'BBB'),
(3,'DDD'),
(4,'KKK')

SELECT  
    A.A,
    B.B,
    CASE ISNULL(B.B,'') WHEN '' THEN 0 ELSE 1 END AS Match
FROM @tblA A
LEFT JOIN @tblB B ON A.A=B.B

Upvotes: 0

Related Questions