Select query on the columns when the column names are retrieved as another query?

Question

I am working on creating a database query in SQL Server to retrieve all the contract item details corresponding to a Contract.

1. A Contract would have multiple items stored in CONTRACT_ITEM table. (1:N)

2. Some of the Contract Items would have Tax associated with them stored in tax table. (1:1)

3. Some of the Contract Items would have an associated tax form column. This column name can be found out by a separate select query (say SELECT1) and would be same for all contract items. For instance, it's 'c1' for one customer, 'c2' for another but each customer has their own set of tables.

4. The columns identified in step-3 i.e 'c1' will store AMAZ_ID.

To get the Contract Items details for a Contract, I have written the below query. But not clear on how to retrieve the AMAZ_CODE for each Contract_Item

select ci.contract_item_id, tax.tax_id 
from contract_item ci
join contract cc on cc.contract_id = ci.contract_id
left join tax on ci.tax_id = tax.tax_id
where cc.contract_id = 1234

Now suppose SELECT1 returns c1 , then for each contract_item need to run the below query also and include in the contract item details.

Below query also needs to cast select

select amaz_code 
from amaz aa 
where aa.amaz_id = (select cast(c1 as int)  from contract_item_tax_form)

Can some point to how to include the AMAZ_CODE for each contract item ?

Answer 1

It appears you are looking for outer apply.

This should get you close to what you are after.

SELECT ci.contract_item_id,
       tx.tax_id,
       tf.amaz_code
FROM contract_item ci
    JOIN Contract cc
        ON cc.contract_id = ci.contract_id
    LEFT JOIN Tax tx
        ON ci.tax_id = tx.tax_id
    LEFT JOIN contract_item_tax_form cift
        ON cift.contract_item_id = ci.contract_item_id
    OUTER APPLY
(
    SELECT TOP (1)
           amaz_code
    FROM amaz aa
    WHERE aa.amaz_id = cift.c1
    ORDER BY amaz_id
) tf
WHERE cc.contract_id = 1234;