Swarup Saha Swarup Saha - 28 days ago 6
SQL Question

Display conditional output with column name using sql/postgresql query

I have three tables:

1> Table_A
2> Table_B
3> Table_C


Table_A contains following

id(PK) ssn
--- ---
1 111
2 222
3 333


Table_B contains following

id (PK) ssn details
---- --- ------
11 111 hi
22 222 hello
33 555 hi hello
44 666 hi hello hi


Table_C contains following

id(PK) ssn mobile flag
----- --- ------ ----
1X 555 9830 A
2X 555 9831 B
3X 666 9745 A
4X 666 9876 B
5X 777 3454 A
6X 777 8734 B


First I need to find which SSN is present in Table_B but not in Table_A

select
distinct b.ssn
from
Table_B b
where
b.ssn not in (select ssn from Table_A)


The above will give me record

ssn
---
555
666


Now I need to print in the following manner from Table_C corresponding to ssn(s) that I get above,

ssn to_mobile from_mobile
--- --------- -----------
555 9830 9831
666 9745 9876


i.e., mobile column will be to_mobile if flag is A and it will be from_mobile if flag value is B.

I am really not able to find this way out. Kindly help

vkp vkp
Answer

Use a left join to get the values in table_b that are not in table_a and then join table_c and use conditional aggregation.

select 
 b.ssn
,max(case when c.flag = 'A' then c.mobile end) to_mobile
,max(case when c.flag = 'B' then c.mobile end) from_mobile
from table_B b
left join table_A a on a.ssn=b.ssn
join table_C c on c.ssn=b.ssn
where a.ssn is null
group by b.ssn

This assumes you have one row each for flags A,B per a given ssn.