CODEWITHSUNDEEP
sqljoindynamic
user2216540
user2216540

Reputation: 83

SQL - Create a join / query based on the results of another query. The Join table would be from the results of another query

I hope you can help me.

I need to create a query which will use the values from a dynamic result to use as the left join table.

For example:

This query will return different values all the time. Based on the each @itemid.

SELECT DISTINCT [Reference]
FROM TABLE_ABC
WHERE ID = @ItemId

Then using this 'Reference' values, I need to left join on that values and also return the sum for that as for the query below. The query below works perfect, if I hard coded the Reference in each left join. But I need to make this all dynamic that it will create the query depending on the number of Reference items it returns.

SELECT 
SUM(x.Value)  [Ref A],
SUM(x1.Value) [Ref B],
SUM(x2.Value) [Ref C]
FROM TABLE_YZ yz
LEFT JOIN TABLE_ABC x   ON yz.id = x.id AND x.Reference = 'Ref A'
LEFT JOIN TABLE_ABC x1  ON yz.id = x.id AND x1.Reference = 'Ref B'
LEFT JOIN TABLE_ABC x2  ON yz.id = x.id AND x2.Reference = 'Ref C'

Could anybody give me some advice on how to create this script? Or what shall I search for?

Thanks a lot in advance.

Anderson

** Sample Results:

Total ---- Ref A ---- Ref B ---- Ref C ---- Ref D ---- Ref E ---- and so on…..

1,000.00 ---- 100.00 ---- 200.00 ---- 300.00 ---- 250.00 ---- 250.00

Upvotes: 0

Views: 2855

Answers (2)

Monofuse
Monofuse

Reputation: 827

Is this kinda of what you need?

SELECT 
SUM(x.Value)  [Ref A],
SUM(x1.Value) [Ref B],
SUM(x2.Value) [Ref C],
SUM(x3.Value) [Ref D]
FROM TABLE_YZ yz
LEFT JOIN TABLE_ABC x   ON yz.id = x.id AND x.Reference = 'Ref A'
LEFT JOIN TABLE_ABC x1  ON yz.id = x.id AND x1.Reference = 'Ref B'
LEFT JOIN TABLE_ABC x2  ON yz.id = x.id AND x2.Reference = 'Ref C'
LEFT JOIN (SELECT DISTINCT [Reference]
           FROM TABLE_ABC
           WHERE ID = @ItemId) x3 ON yz.id = x.id AND x3.Reference = 'Ref D'

Upvotes: 3

xQbert
xQbert

Reputation: 35323

I'm unsure why something like this wouldn't work. It gives you rows instead of columns...

Select Sum(x.value), reference
from table_abc
where ID = @itemID
group by reference

If you need columns, you could use dynamic SQL and pivot the columns. or if you always know there will be 3 references, we can do something different with case statements and a row number.

Upvotes: 1

Related Questions