TSQL Query Inner Selects from Outer Where Clause

Question

I am working on a dynamic query and I am a little unsure how to accomplish this task. It seems like something that would be common so I will use this as a learning experience.

My Structure has several tables. I have included a few of them for this example as once I can get the base query down, I can add on to it.

In my final query, the WHERE clause will be generated dynamically.

Here is a fiddle of my structure: http://sqlfiddle.com/#!6/2b104

In the inner select c. you will notice I have a column called localeID. I need to be able to query this in my outer WHERE clause.

For example, that localeID will link to the localeCodes table and from there, I have another table called locations. End result would be saying "Show me everything in North America". Well, we know localeID 8 = Utah and Utah is in North America (when joined to the locations table).

Here is the query to keep it with the OP:

SELECT a.[trainingEventID],
   a.[teTitle],
   a.[teSource],
   a.[teType],
   a.[teMedium],
   a.[teFlag],
   a.[teCreator],
   a.[teCreated],
   a.[tePOC],
   a.[teDirector],
   a.[teTeammateImpact],
   a.[teCustomerImpact],
   a.[teComplexity],
   a.[intID],
   a.[teNeededBy],
   a.[approver],
   a.[approvalDate],
   (SELECT b.[trainingEventID],
           b.[segmentDate],
           b.[nonProdHrs],
           (SELECT c.[segmentID],
                   c.[localeID],
                   c.[teammateCount],
                   c.[leaderCount]
            FROM   BS_TrainingEvent_SegmentDetails AS c
            WHERE  c.[segmentID] = b.teSegmentID
            FOR    XML PATH ('detail'), TYPE, ELEMENTS, ROOT ('details'))
    FROM   BS_TrainingEvent_Segments AS b
    WHERE  b.trainingEventID = a.[trainingEventID]
    FOR    XML PATH ('segment'), TYPE, ELEMENTS, ROOT ('segments'))
FROM   BS_TrainingEvents AS a
--WHERE c.[localeID] = '8'
FOR    XML PATH ('event'), TYPE, ELEMENTS, ROOT ('events');

Answer 1

How about we also join to those two subordinate tables so we can narrow the result set based on your c.localeID:

SELECT  a.[trainingEventID],
        a.[teTitle] ,
        a.[teSource] ,
        a.[teType] ,
        a.[teMedium] ,
        a.[teFlag] ,
        a.[teCreator] ,
        a.[teCreated] ,
        a.[tePOC] ,
        a.[teDirector] ,
        a.[teTeammateImpact] ,
        a.[teCustomerImpact] ,
        a.[teComplexity] ,
        a.[intID] ,
        a.[teNeededBy] ,
        a.[approver] ,
        a.[approvalDate] ,
        ( SELECT    b.[teSegmentID],
                    b.[trainingEventID] ,
                    b.[segmentDate] ,
                    b.[nonProdHrs] ,
                    ( SELECT    c.[segmentID] ,
                                c.[localeID] ,
                                c.[teammateCount] ,
                                c.[leaderCount]
                      FROM      BS_TrainingEvent_SegmentDetails AS c
                      WHERE     c.[segmentID] = b.teSegmentID
                      AND       c.segmentID = tesd.SegmentID
                    FOR
                      XML PATH('detail') ,
                          TYPE ,
                          ELEMENTS ,
                          ROOT('details')
                    )
          FROM      BS_TrainingEvent_Segments AS b
          WHERE     b.trainingEventID = a.[trainingEventID]
          AND       b.trainingEventID = tes.trainingEventID
        FOR
          XML PATH('segment') ,
              TYPE ,
              ELEMENTS ,
              ROOT('segments')
        )
FROM    BS_TrainingEvents a
INNER JOIN BS_TrainingEvent_Segments tes ON a.trainingEventID = tes.trainingeventID
INNER JOIN BS_TrainingEvent_SegmentDetails tesd ON tes.teSegmentID = tesd.SegmentID
INNER JOIN BS_LocaleCodes as locale ON tesd.localeID = locale.localeID
INNER JOIN BS_Locations as loc ON loc.location = locale.location
WHERE loc.[location] = 'Arizona'
FOR     XML PATH('event') ,
            TYPE ,
            ELEMENTS ,
            ROOT('events');