coolstoner coolstoner - 4 months ago 7
SQL Question

How to get columns from different table B based on condition of column on table A?

suppose i have two tables table A and table B

Table A

id name remarks
4 X XXX
6 Y YYY
7 Z ZZZ


Table B

id Aid remarks edit_flag
1 4 NULL 0
2 6 YY changes 1
3 7 Z cahnged 1


so, i would like to retrieve data like below:

if edit_flag is 1 (that is edited), get remarks column from table B else(edit_flag is 0) get remarks column from table A since it isnt edited

i am looking at something like this

if(edit_flag == 0)
then get remarks from table A
else get remarks from table B


so my result table should be looking like

Row_Counter remarks
1 XXX
2 YY changes
3 Z changed

Answer

Use CASE:

SELECT aID = a.id, name,
       remarks = CASE b.edit_flag 
                    WHEN 0 THEN a.remarks
                    WHEN 1 THEN b.remarks
                 END
FROM TableA a INNER JOIN TableB b ON a.id = b.Aid