CODEWITHSUNDEEP
sqlsql-serverrelational-database
coolstoner
coolstoner

Reputation: 799

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

Upvotes: 0

Views: 173

Answers (3)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use Case Statement

    declare @tblA as table
(
    id int,
    name varchar(50),
    remarks varchar(50)
)


insert into @tblA values(4,'x','xxx');
insert into @tblA values(6,'y','yyy');
insert into @tblA values(7,'z','zzz');

declare @tblB as table
(
    id int,
    Aid int,    
    remarks varchar(50),
    edit_flag int
)


insert into @tblB values(1,4,NULL,0);
insert into @tblB values(2,6,'yy changes',1);
insert into @tblB values(3,7,'z changes',1);

SELECT 
    B.id,
    B.Aid,
    B.edit_flag,    
    CASE WHEN edit_flag=1 THEN B.remarks ELSE a.remarks END as remarks
FROM @tblB B
LEFT JOIN @tblA A ON B.Aid=A.id

Upvotes: 0

Mert Gülsoy
Mert Gülsoy

Reputation: 2919

You can join 2 tables and make a conditional query. Such as:

NOTE: I'm assuming that you're using a database supporting "iif". Otherwise you should use "case" as in the other answers.

Select a.id as id_id, iif(b.edit_flag = 0, a.remarks, b.remarks) as remark
  from tableA as a inner join tableB as b on a.id=b.Aid

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

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

Upvotes: 5

Related Questions