Get values from all sub-divided child tables.

Answer 1

Someone might have a better response, but that's pretty much it. You could opt for COALESCE instead of ISNULL which permit a variable number of arguments, so you can add a third option if both are Email and Documents are NULL for some reason.

Everything that follows is just commentary on the schema. The table structure has a problem, but I'm sure you're now coding after these tables are already established, so this isn't necessarily a call for action. You probably have to live with them as they are.

My instinctive response would have been to assign TransactionId to the child tables, because they are not formally children right now. They are autonomous objects that TransactionTable happens to refer to.

I had similar problem before where I had a key column that didn't have a clear definition and I eventually opted against it. It's not possible to build a formal constraint/foreign key for FileNo on TransactionTable, because FileNo could be defined on either of the two tables.

(Incidentally your status = 'P' check is missing from your query.)

Also if you keep adding new filetype beyond 'E' and 'D' you are going to have to keep extending the query to new tables. A File table of some form, with the key fields on might have been one way of resolving this. [for all I know you may already have some sort of File table]

Not sure if any of this helps you, though. There's no way to improve upon your query without changing the table structures.

Answer 2

Your query looks good. The only comment I'd make is that I don't see you satisfying the Status='P' condition that you specified in your requirements.

Select A.TransactionID, IsNull(B.ReceivedDate, C.ReceivedDate)  as ReceivedDate, A.Value
    From TransactionTable as A 
        Left outer join EmailTable as B 
            on A.FileNo = B.EmailFileNo 
                and A.FileType='E'
        Left outer join DocumentsTable as C 
            on A.FileNo = C.DocFileNo 
                and A.FileType = 'D'
    where A.Status = 'P'