select a value from different tables depending on a condition I am working on an inventory system. We have stored transactions in a transactions table which stores (challanid, challandate, toid, fromid, toenittyid). Toentity can be a branch, distributor, dealer or stockist which are stored in different tables. I need to build a query for transactions over a period of time which shows all transactions. For this I need to show entityname which needs to be withdrawn by toid from respecitve dealer/distributor or stockist tables depending on toenitytid. 

For example, if toentityid is 2, we need to get branchname from branch table, if toentityid is 3 we need to get stockistname from stockist table by toid and so on. I tried switch case, but that does not work to change the table name. I need help to complete this query. Thanks a lot for your help.
You could use something approximately like this; I think you will see how.

CREATE VIEW TransactionEntity 
AS
SELECT 2 AS EntityType, Branch.* FROM Branch
UNION ALL
SELECT 3 AS EntityType, Stockist.* FROM Stockist
UNION ALL
SELECT 4 AS EntityType, ... 


The tables may have different column names from each other, in which case you shouldn't use "*".  The column names of the view are the column names assigned in the first SELECT statement, but those can be changed from actual column names.

A different method, more complicated, more efficient (I think), is this:

SELECT 
      tr.*
    , COALESCE(br.branchname, st.stockistname, - other columns - )
      AS entityname
FROM
transactions     tr
LEFT OUTER JOIN
branch           br
ON
( tr.toentity = 2 AND tr.toid = br.branchid )
LEFT OUTER JOIN
stockist         st
ON
( tr.toentity = 3 AND tr.toid = st.stockistid ) -- corrected mistake of ':' should be '.'
LEFT OUTER JOIN
- other tables that are needed -


The result of COALESCE is the first of its terms that is not NULL.  So, if the transaction is to a branch, then branchname produces the value.  If the transaction is to a stockist, then branchname is NULL but stockistname produces the value.  And so on, for the other tables that may hold the data that you need.

(Updated - typing mistake in the code example, corrected, see the comment)