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.
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)
I am struggling to understand the question, but if you need different data returned, you can put CASE statements into the SQL ( depending on your database ), to return different fields based on conditions.
You might have to look up the precise details based on your database.
精彩评论