if and else in select query in sql

Question

I have two tables one with employee information in people_table and one with the phone no. of these employees. Now the phone_table can have multiple entry for a particular employee with different types like W1-->Work,M-->Mobile,H1-->Home. Now i want to wriet query such that if W1 is enetered then no other phone type should be fetched. If W1 is not enetered then M should be checked and query should fetch mobile number instead(so on and so forth) so basically i have to use if and else in select statement . I cannot write a function for this as per requirement.

I tried using Case and when like follows :-

select emp_num, emp_name,ph_no,ph_type,
case 
  when ph_type='W1'
then ph_no
  when ph_type='M'
then ph_no

from people_table pt ,phone_table ppt
where pt.person_id=ppt.person_id

But this is also returning the same output. that is if an wmployee has w1 no. and M no. it will return both.. but it should fetch W1 no. only and not M

D Stanley · Accepted Answer · 2014-11-20 19:40:56Z

4

It sounds like you need to join to a filtered set of the phone table twice:

select 
    emp_num, 
    emp_name,
    ph_no,
    COALESCE(ppt_w.ph_type,ppt_m.ph_type) ph_type,
    COALESCE(ppt_w.ph_no,ppt_m.ph_no) ph_no
from people_table pt 
LEFT JOIN phone_table ppt_w
  ON pt.person_id=ppt_w.person_id
     AND ppt_w.ph_type='W1'
LEFT JOIN phone_table ppt_m
  ON pt.person_id=ppt_m.person_id
     AND ppt_m.ph_type='M'

answered Nov 20, 2014 at 19:40

D Stanley

153k12 gold badges190 silver badges257 bronze badges

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

Comments

Guffa · Accepted Answer · 2014-11-20 19:42:39Z

1

You can do left joins to get the phone numbers, and coalesce to get the first phone number that exists:

select
  p.emp_num,
  p.emp_name,
  coalesce(w1.ph_no, m.ph_no, h1.ph_no) as ph_no,
  coalesce(w1.ph_type, m.ph_type, h1.ph_type) as ph_type
from
  people_table p
  left join phone_table w1 on w1.person_id = p.person_id and w1.ph_type = ''
  left join phone_table m on m.person_id = p.person_id and m.ph_type = ''
  left join phone_table h1 on h1.person_id = p.person_id and h1.ph_type = ''

answered Nov 20, 2014 at 19:42

Guffa

703k112 gold badges760 silver badges1k bronze badges

Collectives™ on Stack Overflow

if and else in select query in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related