Select * from (if not exists () else )-CodePudding

how can one join one table with the result of the condition?

the first query takes all the information from Table 1

SELECT ID, Name, Blob From [Table-1]

2)The second request checks if the value is by ID with the required status or not. If there is, it takes the first one with the required status, if there is no such status, it takes any first one for this ID.

IF EXIST (select * from [Table-2] where ID = *anyID* and STATUS = *anyStatus*) 
 begin 
    SELECT TOP 1 * from [Table-2] where ID = *anyID* and STATUS = *anyStatus* 
 end 
else 
 begin 
    SELECT TOP 1 * from [Table-2] where ID = *anyID* 
 end

As a result, I need a general table that has all the columns from Table 1 and the values from Table 2, if any. I tried to do it through JOIN, but something went wrong

SELECT t1.ID, t1.Name, t1.Blob From [Table-1] as t1
LEFT JOIN 
(
   IF EXIST (select * from [Table-2] where ID = *anyID* and STATUS = *anyStatus*) 
     begin 
        SELECT TOP 1 * from [Table-2] where ID = *anyID* and STATUS = *anyStatus* 
     end 
   else 
     begin 
        SELECT TOP 1 * from [Table-2] where ID = *anyID* 
     end
   ) as t2
ON t1.ID = t2.ID

CodePudding user response：

You can use apply:

SELECT t1.ID, t1.Name, t1.Blob, t2.*
From [Table-1] t1 OUTER APPLY
     (SELECT TOP (1) t2.*
      FROM [Table-2] t2
      WHERE t2.id = t1.id 
      ORDER BY (CASE WHEN t2.status = ? THEN 1 ELSE 2 END)
     ) t2;

This returns one row in table 2, with preference for the desired status.