CODEWITHSUNDEEP
sqlpostgresql
Swarup Saha
Swarup Saha

Reputation: 995

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

Upvotes: 1

Views: 2133

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can continue the path you are down:

select c.ssn,
       max(case when c.flag = 'A' then c.mobile end) as to_mobile,
       max(case when c.flag = 'B' then c.mobile end) as from_mobile
from table_c c
where c.ssn in (select b.ssn
                from Table_B b
                where b.ssn not in (select ssn from Table_A)
               )
group by c.ssn;

That said, vkp's answer without subqueries is also a very typical approach to this type of problem.

Actually, as I think about it, your condition is that c.ssn is in B but not A, so I would go for:

select c.ssn,
       max(case when c.flag = 'A' then c.mobile end) as to_mobile,
       max(case when c.flag = 'B' then c.mobile end) as from_mobile
from table_c c
where c.ssn in (select b.ssn from Table_B b) and
      c.ssn not in (select a.ssn from Table_A a)
group by c.ssn;

Somehow, I find this the easiest to follow logically.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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.

Upvotes: 2

Related Questions