CODEWITHSUNDEEP
sqlsql-server
Munna Donepudi
Munna Donepudi

Reputation: 61

Case Statement With Multiple Joins

I have two tables, emp and location. I need to fetch the records for all the matching eid s' of emp table based on location type.

If the location type=2 then we need to fetch the city associated with it.

If we don't have type=2 record we need to fetch type=1 associated city for the matching eid.

My case statement works fine until there are two records for the eid of both type 1 and type 2. But I need to fetch only type 2 in this case

select case when a.type=2 then a.city
When a.type=1  then a.city
Else '0' End As City
From location a
Join emp r
On a.eid=r.eid

emp table
eid  ename
1    james
2    mark
3    kristie
4    john
5    allen


location table

  city     eid  type
  athens    1   2
  melbourne 2   1
  london    2   2
  newyork   3   1

output:

eid ename  city  type
1   james  athens   2 
2   mark   london   2 
3   kristie newyork 1

Upvotes: 1

Views: 4751

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to rank your cities. Use ROW_NUMBER to do that:

select e.eid, e.name, l.city, l.type
from emp e
join
(
  select 
    city, eid, type,
    row_number() over (partition by eid order by type desc) as rn
  from location
) l on l.eid = e.eid and l.rn = 1;

rn is 1 for the better city per eid (where "better" is the one with the higher type).

Upvotes: 0

Jeremy Real
Jeremy Real

Reputation: 766

Try this:

select a.eid
      ,r.ename
      ,case when a.type=2 then b.city
            when a.type=1  then b.city
            else '0' End As City
from (
select a.eid, max(a.type) as type
From location a
group by a.eid
) a
right outer join location b
on a.eid = b.eid and a.type=b.type
inner join emp r
on b.eid=r.eid

Upvotes: 1

Mark Adelsberger
Mark Adelsberger

Reputation: 45649

I think the most direct way to represent what you're asking for is:

select coalesce(l2.city, l1.city, '0') as city
  From           emp r
       left join location l1
              on l1.eid = r.eid
             and l1.type=1
       left join location l2
              on l2.eid = r.eid
             and l2.type=2

The subquery-based solution proposed by Jeremy Real may also work, but it assumes that 1 and 2 are they only values in the table for location.type (and I just don't find it to be as intuitive).

Upvotes: 2

Related Questions