Is it possible to use conditional statements such as if / then / if this is the case in SQL?

Question

I have an SQL query that has an id field - consider it a foreign key. I need to make desicion based on the value of this id field so that:

• If the value is less than 3100, run a subfetch from table B.
• If the value is greater than 3100, run a subfetch from table C.

The operation is as follows:

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 do 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 it possible?

UPDATE: Your answers suggest that I am using left connections. My query already contains several left outer joins, so I don’t know how this will work. Here is the complete request:

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

, , , UNION, (1 , b > 3100, , c ids <= 3100).

( , , ), "" c, 2 b. , , c, "" "" "".

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 2

case:

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

. lastname null, , , , , , , , lastname , ,

Answer 3

CASE , , afaik. , .

, , , , , First Name Last Name, null.

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

Answer 4

( ), . , , .

Answer 5

HLGEM

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

:

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

The number of returned columns cannot be changed in the query (at least on any system that I am familiar with), so if you have a case C situation and there is only a name, I would return it as LastName and set FirstName to an empty string. This can be done NULL if it is better suited to your application.