Display conditional output with column name using sql/postgresql query

Question

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

Vamsi Prabhala · Accepted Answer · 2016-11-11 18:25:10Z

2

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.

answered Nov 11, 2016 at 18:25

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Swarup Saha Over a year ago

Thanks a lot VKP for your prompt answer. Your comment is really helpful achieving my goal.

Gordon Linoff · Accepted Answer · 2016-11-11 18:28:09Z

1

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.

answered Nov 11, 2016 at 18:28

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Swarup Saha Over a year ago

wow! Worked like a charm. Thanks a lot Gordon. You made my day.

Collectives™ on Stack Overflow

Display conditional output with column name using sql/postgresql query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related