Question about if-then and when-then in SQL

Question

Greetings,

I have a sql fetch that has an id field - think of it as a foreign key. I need to make a desicion based on the value of this id field such that if the value is less then 3100 run a nested fetch form table B. If the value is greater then 3100 run a nested fetch from a table C.

The statement looks like this

Select a.ID, a.SN, a.User_Ident,
 (select b.first_name from b where b.ident = a.User_Ident) as 'First Name',
 (select b.last_name from b where b.ident = a.User_Ident) as 'Last Name',
from a
where ....

what I would like to accomplish is something like this:

Select a.ID, a.SN, a.User_Ident,

   when a.User_Ident > 3100 then
         (select b.first_name from b where b.ident = a.User_Ident) as 'First Name',
 (select b.last_name from b where b.ident = a.User_Ident) as 'Last Name'
   else 
         (select c.name from c where c.ident = a.User_Ident) as 'Name'
from a
where ....

Is this possible? Thanks, Eric

UPDATE: Your answers suggested a use left joins. My query already contains several of left outer joins, so I don't know who would this work. Here the complete query:

select
A.Ident, A.Serial_Number, A.Category_Ident, C.Description as Category, A.Purchase_Order, A.Manufacturer_Ident, M.Description as Manufacturer,
A.Hardware_Model, A.Processor_Quantity, A.Processor_Speed_Hertz, A.Memory_Installed_Bytes, A.Memory_Maximum_Bytes, A.Memory_Slots_Used, A.Memory_Slots_Total, A.Storage_Capacity_Bytes, A.Video_Memory_Bytes, A.Screen_Size_Diagonal_Inches,
A.Software_Ident, S.Software_Title,
A.Account_Ident, T.Description as Account,

A.User_Ident,
(select Q.dbo.P.user_name from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'User Name',
(select Q.dbo.P.first_name from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'First Name',
(select Q.dbo.P.last_name from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'Last Name',
(select Q.dbo.R.description from Q.dbo.R where Q.dbo.R.ident =  (select Q.dbo.P.rank from Q.dbo.P where Q.dbo.P.ident = A.User_Ident)) as 'Rank',
(select Q.dbo.P.phone from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'Phone',
(select Q.dbo.P.smtp_address from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'Email',
(select Q.dbo.O.description from Q.dbo.O where Q.dbo.O.ident =  (select Q.dbo.P.organization_ident from Q.dbo.P where Q.dbo.P.ident = A.User_Ident)) as 'Organization',
(select Q.dbo.L.description from Q.dbo.L where Q.dbo.L.ident =  (select Q.dbo.P.location_ident from Q.dbo.P where Q.dbo.P.ident = A.User_Ident)) as 'Location',

A.Disposition_Ident,
D.Description as Disposition,
A.Notes,
A.Updated,
A.UpdatedBy,
A.Label,
A.Scanned,
S.Licensed

FROM Assets 

left outer join C on A.Category_Ident = C.Ident
left outer join M on A.Manufacturer_Ident = M.Ident
left outer join S on A.Software_Ident = S.Ident
left outer join T on A.Account_Ident = T.Ident
left outer join D on A.Disposition_Ident = D.Ident

WHERE ((T.Description like '%' + @Account + '%') or (A.Account_Ident like '%' + @Account + '%'))

order by Serial_Number

Answer 1

CASE can only return a column, not a row, and you can't correlate queries into it afaik. And no matter what, a result set must have a static amount of columns.

I think what you would want would be a result set where if the First Name or Last Name was filled out the Name was null.

a.ID | a.SN | Name | First Name | Last Name
1    | #    | Name | null       | null
2    | #    | null | John       | Doe

Answer 2

Many ways to skin a cat, but I think this approach is worth a try, using a UNION to combine the results of the 2 different conditions (1 query joined to b for ids > 3100, and another query joined to c for ids <= 3100).

You have to return the same fields (you can't as you indicated you wanted), return 1 "name" field when looking at c when you return 2 fields for the b condition. Hence, in this example, when you join to c, it returns "name" as First Name, and returns a blank Last Name value.

Select a.ID, a.SN, a.User_Ident, b.first_name AS 'First Name', b.last_name AS 'Last Name'
FROM a
    JOIN b ON a.User_Ident = b.ident
WHERE (a.User_Ident > 3100)
   AND (......)
UNION ALL
Select a.ID, a.SN, a.User_Ident, c.name AS 'First Name', '' AS 'Last Name'
FROM a
    JOIN c ON a.User_Ident = c.ident
WHERE (a.User_Ident <= 3100)
   AND (......)

Answer 3

Union would work(beat me there), or you could use an inline function as well. Like the others have said you must return same set of fields.

Answer 4

I'd accomplish this with two left joins and a case statement:

select  a.field1, 
        case when b.lastname is not null then b.firstname else c.firstname end,
        case when b.lastname is not null then b.lastname else c.larstname end
from table1 a 
left join table2 b 
    on a.id = b.id
left join table3 c 
    on a.id = c.id

Note I used lastname is null filed in both case staments, becasue tyou probably don;t want the first name form one table and the last name form the other and lastname is less likely to be null than the firstname in the actual table.

Answer 5

A slight variation on HLGEM's answer

select  a.field1, 
        case when b.lastname is not null then b.firstname else c.firstname end,
        case when b.lastname is not null then b.lastname else c.larstname end
from table1 a 
left join table2 b 
    on a.User_Ident > 3100 AND a.id = b.id
left join table3 c 
    on a.User_Ident <= 3100 AND a.id = c.id

Answer 6

My take on the outer join solution is:

SELECT
   a.id
  ,a.ssn
  ,a.user_ident
  ,case when a.user_ident <= 3100 then c.name else b.lastname end   LastName
  ,case when a.user_ident <= 3100 then ''     else b.firstname end  FirstName
 from a
  left outer join b
   on b.ident = a.user_ident
  left outer join c
   on c.ident = a.user_ident

A query cannot vary the number of columns returned (at least not in any system I'm familiar with), so if you have a "case C" situation and only have Name, I would return it as LastName and set FirstName to the empty string. This could be made a NULL, if that fits your application better.