SQL Query from Results of Another SQL Query

[ronnie_roberts]

The way our Agile software is configured is as follows: Themes >> Epics >> Capabilities >> Features >> Stories where each has it's own table. The issue is that we only care about a handful of Themes and therefore don't need the TONS of extra data.  Currently we're just pulling everything into a giant list that includes everything but we end up with Themes being duplicated hundreds of times which can mess up cost calculations etc.  I've also tried just creating the WHERE and JOINs so everything works and am just copying/pasting between the queries.  However, I'm pretty sure there's got to be some way to feed the results of the Theme query into the Epic query.  If a new person joins I'd rather not have to update 8 queries when I could just update one.

My question: is it possible to run a query on Epics where the WHERE condition will come from the results of the Themes table?  Perhaps something like WHERE epics.[FK Theme ID] = {Power BI Themes Result: [Theme ID]} or some other magic formula i'm sure people smarter than me know.

Method 1:

Agile

SELECT
    theme.[Theme ID]
    , theme.[Theme Name]
    , epic.[Epic ID]
    , epic.[Epic Name]
    , epic.[FK Theme ID]
FROM server.Theme AS theme
    JOIN server.Epic AS epic ON theme.[FK Theme ID] = epic.[Epic ID]
WHERE theme.[Portfolio Manager] IN ('Person 1', 'Person 2')

Method 2:

Themes

SELECT
    theme.[Theme ID]
    , theme.[Theme Name]
    , theme.[Portfolio Manager]
FROM server.Theme AS theme
WHERE theme.[Portfolio Manager] IN ('Person 1', 'Person 2')

Epics

SELECT
    epic.[Epic ID]
    , epic.[Epic Name]
    , epic.[FK Theme ID]
FROM server.Theme AS theme
    JOIN server.Epic AS epic ON theme.[FK Theme ID] = epic.[Epic ID]
WHERE theme.[Portfolio Manager] IN ('Person 1', 'Person 2')

I pray this all makes sense.  Thanks in advance!

[lbendlin]

Are you looking for CTEs?

[ronnie_roberts]

I'm trying to avoid having to update 8 SQL queries every time I want to update the WHERE clause for the Themes.  I'm assuming Power BI has some functionality where they would run the SQL queries sequentially and using one to feed into the next.

[lbendlin · Accepted solution]

It does, but you will wake up the firewall monster as this will result in cross query data bleed. Don't expect to get this to work in the Power BI service.

[ronnie_roberts]

Unfortunate.  Thanks for the feedback!