Get values from all sub-divided child tables

I have three tables as below.

TransactionTable
----------------
TransactionID
Status
Value
FileNo      (int)
FileType        - 'E' i开发者_开发知识库ndicates Email,  'D' Indicates Document


EmailTable
----------
EmailFileNo (Identity)
ReceivedDate
....
....
....



DocumentsTable
---------------
DocFileNo   (Identity)
ReceivedDate
.....
.....

There is one to many relationship between EmailTable and TransactionTable and also between DocumentsTable and TransactionTable

What is the name for such type of relationship... I just used the term sub-divided child tables

I need to select TransactionID, ReceivedDate, Value where status is 'P'...

I could get the result using

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' 

The above query gives me the result as expected... Is this the way it should be done or is there a better way to handle such scenarios ?

Edit : Included the where clause, which got missed during copy paste operation. Thanks for pointing this out.

---

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'

---

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.