Swarup Saha Swarup Saha - 11 months ago 42
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

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

The above will give me record


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 Source

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.

,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.