Below are the sample tables for your reference.
TableA
| Col_A | Col_B |
|---|---|
| AAA | Active |
| BBB | InActive |
TableB
| Col_A | Col_C | Col_D |
|---|---|---|
| AAA | D_Fault | 1 |
| AAA | C_Fault | 1 |
| AAA | CH_Fault | 0 |
| BBB | D_Fault | 1 |
| BBB | C_Fault | 0 |
The common column between table A and B is Col_A.
I need the below output.
| Col_A | Col_B | D_Fault_Status | C_Fault_Status | CH_Fault_Status |
|---|---|---|---|---|
| AAA | Active | Faulty | Faulty | Not Faulty |
| BBB | InActive | Faulty | Not Faulty | Not Faulty |
The logic is that, when Col_D is 1 has any specific value in Col_C then the Status must be Faulty for the respective Col_A. If Col_C is 0 or there is no record for the respective Fault, it can be Not Faulty. But the result must be in the same row for each Col_A which is unique.
I tried using select along with case expression, but it gives multiple rows as output, when tried with MAX(Case….), it looks for the max from the rows instead of all the row values.
I tried something like this, but it didn’t help:
SELECT TableA.Col_A,
Col_B,
MAX(CASE WHEN Col_D = '1'
AND Col_C IN (D_Fault, E_Fault) THEN 'Faulty'
WHEN Col_D = '0' THEN 'Not Faulty'
END) AS D_Fault_Status,
MAX(CASE WHEN Col_D = '1'
AND Col_C IN (C_Fault, EH_Fault) THEN 'Faulty'
WHEN Col_D = '0' THEN 'Not Faulty'
END) AS C_Fault_Status,
MAX(CASE WHEN Col_D = '1'
AND Col_C IN (CH_Fault, CHE_Fault) THEN 'Faulty'
WHEN Col_D = '0' THEN 'Not Faulty'
END) AS CH_Fault_Status
FROM TableA
JOIN TableB ON TableA.Col_A = TableB.Col_A;
Jai Krish is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
9
If it’s always these 3 statuses, it would be easier to do this:
select
a.COL_A
, a.COL_B
, case when max(case when Col_C = 'D_Fault' then Col_D end) = 1 then 'Faulty' else 'Not Faulty' end as D_Fault_Status
, case when max(case when Col_C = 'C_Fault' then Col_D end) = 1 then 'Faulty' else 'Not Faulty' end as C_Fault_Status
, case when max(case when Col_C = 'CH_Fault' then Col_D end) = 1 then 'Faulty' else 'Not Faulty' end as CH_Fault_Status
from TableA a
left join TableB b on a.COL_A = b.COL_A
group by a.COL_A, a.COL_B
Output:
| COL_A | COL_B | D_Fault_Status | C_Fault_Status | CH_Fault_Status |
|---|---|---|---|---|
| AAA | Active | Faulty | Faulty | Not Faulty |
| BBB | InActive | Faulty | Not Faulty | Not Faulty |
| CCC | InActive | Not Faulty | Not Faulty | Not Faulty |
fiddle
Be aware if Col_C includes any other values than those 3 above, they won’t be considered by this query.
0