if else in inner join for select query-CodePudding

I want to perform select query if else in innner join if IsStatus =0 then i want to inner join with T_valDob and show val column from that table. if IsStatus =1 then i want to inner join with T_valSob and show val column from that table. IsStatus is bit type.

T_MappingTable

MappingID	valID	IsStatus
0	1	0
1	2	0
2	3	0
3	4	0
4	5	0
5	1	1
6	2	1
7	3	1
8	4	1
9	5	1
10	6	1

T_valDob

valID	val
1	val1
2	val2
3	val3
4	val4
5	val5
6	val6
7	val7
8	val8

T_valSob

valID	val
1	valSop1
2	valSop2
3	valSop3
4	valSop4
5	valSop5
6	valSop6
7	valSop7
8	valSop8

Like i want final output like this in single resultset

MappingID	valID	IsStatus	val
0	1	0	val1
1	2	0	val2
2	3	0	val3
3	4	0	val4
4	5	0	val5
5	1	1	valSop1
6	2	1	valSop2
7	3	1	valSop3
8	4	1	valSop4
9	5	1	valSop5
10	6	1	valSop6

CodePudding user response：

I would think you probably want to do a UNION query, such as:

SELECT MT.MappingID, MT.valID,MT.IsStatus, TD.val
FROM T_MappingTable MT
INNER JOIN T_valDob TD on MT.valID = TD.valID
WHERE MT.IsStatus = 0

UNION ALL

SELECT MT.MappingID, MT.valID,MT.IsStatus, TS.val
FROM T_MappingTable MT
INNER JOIN T_valSob TS on MT.valID = TS.valID
WHERE MT.IsStatus = 1

CodePudding user response：

I don't think that this can be done with two INNER joins because no row from T_MappingTable can have both IsStatus = 0 and 1.

I think you want two LEFT joins with a CASE as part of your SELECT something like this:

SELECT TMap.MappingID,
       TMap.valID,
       TMap.IsStatus,
       CASE WHEN TMap.IsStatus = 0 THEN TDob.val ELSE TSob.val END AS val
FROM T_MappingTable AS TMap
     LEFT JOIN T_valDob AS TDob ON TMap.valID = TDob.valID AND TMap.IsStatus = 0
     LEFT JOIN T_valSob AS TSob ON TMap.valID = TSob.valID AND TMap.IsStatus = 1;