Problem is forming Query

[Aradhana]

Hello,

I have a table
say SalesLeads. This table is accessed through various front -ends and
it stores data for Sales leads of various products.

This table has a reference to another table(SalesLead RefTable)

Sample Data in SalesLeads

SalesLeadID Comments RefTableID
-------------------------------------------------------
1 Sample 1

Sample Data in SalesLeadRefTab le

ReferenceID TableName TableFieldName
-------------------------------------------------------
1 ProductTable ProductID
I need to form a query which will refer to the refdtableID and form a
dynamic query like

Select Comments , TableName.Table FieldName from SalesLeads,Tabl eName

Please let me know if this can be doen in 1 query

[David Portas]

You could implement a JOIN to one of many tables like this:

SELECT A.keycol, COALESCE(B.col1 , C.col1, D.col1) AS col1
FROM Something AS A
LEFT JOIN Table1 AS B
ON A.keycol = B.keycol AND A.entity = 1
LEFT JOIN Table2 AS C
ON A.keycol = C.keycol AND A.entity = 2
LEFT JOIN Table3 AS D
ON A.keycol = D.keycol AND A.entity = 3

Otherwise you would have to use Dynamic SQL inside an EXEC statement.

--
David Portas
SQL Server MVP
--