SQL Select A row conditionally

Question

I was wondering if it was possible to do something like the following in SQL.

Assume you have three tables. Field Id is the relational key through all database

Table1 - PK Id, .... 
Table2 - PK Id, T3FK, TimeStamp,....
Table3 - PK Id, identity, Status, ....

Table1.id (1 to *) Table 2.id
Table1.id (1 to *) Table 3.id
Table2.id+Table2.T3FK (1 to *) Table3.Id+Table3.identity

I am trying to combine the following select statements:

SELECT T1.*, T2.* 
  FROM Table1 T1
  JOIN Table2 T2 ON T1.Id = T2.Id
  JOIN Table3 T3 on T1.Id = T3.Id
 WHERE T3.Status = 'A' 
   AND T2.P3Fk = T3.identity 
   AND T1.id IN (SELECT Id 
                   FROM ListOfIds)

...and:

SELECT T1.*, T2.* 
  FROM Table1 T1
  JOIN Table2 T2 ON T1.id = T2.id
  JOIN (SELECT id, MAX(TimeStamp) as TimeStamp 
          FROM Table2 
      GROUP BY id) t2a ON t2.id = t2a.id 
                      AND t2.TimeStamp = t2a.TimeStamp
 WHERE t1.Id IN (SELECT Id 
                   FROM ListOfIds)

So basically what I am trying to do is have one select statement that returns the row in T2 where T2.P3FK = T3.identity and T3.status = 'A' and if that does not exists then return the row in T2 that has the latest timestamp. I need to do this for a list of ids as well not just one id.

Answer 1

Probably the easiest way is to modify the JOIN to T2 and T3 and add the JOIN to t2a and then use either case or coalesce on all the fields you need from T2.

SELECT T1.*, 
   COALESCE(T2.id,t2a.id) id,
   COALESCE(...
  FROM Table1 T1
  LEFT JOIN (Table2 T2
     INNER JOIN Table3 T3 on T1.Id = T3.Id
             and  T3.status = 'A' )
   ON T1.Id = T2.Id
  INNER JOIN (SELECT id, MAX(TimeStamp) as TimeStamp 
          FROM Table2 
      GROUP BY id) t2a ON t2.id = t2a.id 
                      AND t2.TimeStamp = t2a.TimeStamp
 WHERE t1.Id IN (SELECT Id 
                   FROM ListOfIds)