CASE statement in FROM clause

Question

See below DDL:

CREATE TABLE dbaddress
(aid integer identity not null, link_id int, link_type char, primary key (aid))
CREATE TABLE dbDoorSupervisor
(did integer identity not null, name varchar(30), primary key (did))
CREATE TABLE dbLicensee
(lid integer identity not null, name varchar(30), primary key (lid))

INSERT INTO dbDoorSupervisor (name) values ('Ian')
INSERT INTO dbLicensee (name) values ('Maria')
INSERT INTO dbaddress (link_id, link_type) values (1,'D')
INSERT INTO dbaddress (link_id, link_type) values (1,'L')

I am trying to get the name of the door manager or Licensee depending on the Address.AID address that is supplied. For example, if help 1 is provided in the WHERE clause, then Ian is returned from the door manager table, however if help 2 is provided in the WHERE clause, then Maria is returned from the Licensee table.

I know that you can use CASE statements in the SELECT clause, but can you use them in the FROM clause ie to join from the address to the licensee or to the door manager, depending on the AID provided?

Answer 1

you can enable the section left outer joinas follows:

select
    isnull(d.name, l.name) as name
from dbaddress as a
    left outer join dbDoorSupervisor as d on d.did = a.link_id and a.link_type = 'D'
    left outer join dbLicensee as l on l.lid = a.link_id and a.link_type = 'L'

case

select
    case a.link_type
        when 'D' then d.name
        when 'L' then l.name
    end as name
from dbaddress as a
    left outer join dbDoorSupervisor as d on d.did = a.link_id
    left outer join dbLicensee as l on l.lid = a.link_id

, , :

select
    c.name, c.address, c.second_name
from dbaddress as a
    left outer join dbDoorSupervisor as d on d.did = a.link_id
    left outer join dbLicensee as l on l.lid = a.link_id
    outer apply (
        select d.name, d.second_name, d.address where a.link_type = 'D' union all
        select l.name, l.second_name, l.address where a.link_type = 'L'
    ) as c

Answer 2

select a.linkd_id,
case when link_type = 'D' then d.name
    when link_type = 'L' then l.name
end as 'Name'
from dbAddress a
left join dbDoorSupervisor d on d.did = a.link_id
left join dbLicensee l on l.lid = a.link_id

Answer 3

Not without dynamic SQL, which has its own set of problems.

If your database is simple, like this, with only two or so features, one easy way is to join both and process the results manually, for example:

select
    a.aid
    ,case a.link_type
        when 'D' then ds.name
        when 'L' then l.name
    end [name]
from
    dbaddress a
left join
    dbDoorSupervisor ds on a.link_type = 'D' and a.link_id = ds.did
left join
    dbLicensee l on a.link_type = 'L' and a.link_id = l.lid