Multiple sub-selects . . .

[Robeen]

Hi,



I've encountered a query [SQL Server] that has multiple sub selects and I wonder if I can get some help in how to approach/understand it.
I'd appreciate any pointers.

I've summarized the sql to remove fieldnames etc - but what I need is just an overview of how to approach sql with so many subselects.
Currently, I am kind of reading each select level and trying to figure out what's going on and what it is feeding to the previous level.
But I'm basically scratching my head . . .

The sql below is not meant to be syntactically or structurally correct - just a rough idea of all the nested sub-selects ...
Thanks in advance!!

Select * from (SELECT fldnames, ...
  FROM
    (SELECT fldnames, ...
       FROM G_TS AS gt
         INNER JOIN Statements ...
   LEFT JOINS ...
   INNER JOIN ...
     (SELECT ...
        FROM ...
          INNER JOIN ...
          INNER JOIN
              (SELECT ...
               FROM ...
               INNER JOIN ...
               WHERE (BR.fldname = 'Y')) AS a
   LEFT OUTER JOIN
  (SELECT ...
   FROM
    (SELECT fldnames...
     CAST(FLS.fldname AS dec(18, 2)) AS FeeTot
     CAST(DLY.fldname AS dec(18, 2)) AS LAmt,
     CAST (
           (SELECT FLS.fldname / DY.fldname * 100 AS TB) AS DEC(5, 3)) AS TotalBS
            FROM tblname
            INNER JOIN ...
            LEFT OUTER JOIN ..
            WHERE (Ff.HC <> 'sometext')
            AND (FS.fldname = ###)
            AND (FV.fldname <> 0)) AS b_2) AS c ON c.LUM = a.LUM
            LEFT OUTER JOIN
               (SELECT fldnames ...
                FROM
                  (SELECT fldnames
                   FROM LPRCADJ AS L
                   INNER JOINS
                   LEFT OUTER JOIN ...
                   WHERE (fldname IN
                     (SELECT ...
                      FROM
                        (SELECT fldname,
                         CONVERT(date, MODIFY_DATE) AS MODIFY_DATE
                         FROM LCDJ AS l2) AS b_1
                         WHERE (LNUM = a_1.LM)
                         AND (MOD_DTE BETWEEN @StDte AND @EndDte)))) AS d_1 ON d_1.LN = a.LN
                  LEFT JOIN
                           (SELECT ...
                            FROM 
          (SELECT DISTINCT fldnames
                                FROM RMT) AS a 
                                LEFT OUTER JOIN
                                   (SELECT  fldnames
                                    FROM tblname
                                    WHERE charindex('SM', fldname, 1)> 0) AS b ON a.LNM = b.LNM and a.slno = b.RSNO
                                    where b.RK_TYP is not null                                
GROUP BY TTSG, b.rmk_typ, a.lnum) x 
on x.lnum = a.LNUM
where a.PRMB = 'Y'
AND (a.[CHKLNST] IS NULL OR a.[CHKLNST] = 'N')) z

[rpeare]

if the syntax isn't there it'll be hard to point you in the right direction, but typically any time you see a SELECT statement you could theoretically pull that bracketed code out and create a new query to see what it does.

If you work from the innermost SELECT statement outward you will probably have a better idea of how things are relating to one another, this query is likely pulling data from several different tables while also trying to apply criteria to each of the subtables. If you were going to do this the ugly way you could create a sub query for each SELECT statement then build the query using all your subqueries.

[Robeen]

Thanks, rpeare!
Are long SQL queries [like the one in my example - with multiple subqueries] in any way the equivalent of what I have done often in MS Access - i.e. create queries built on queries that are themselves built on queries - sometimes many levels deep?

[June7]

I would say yes. This one long nested SQL is basically akin to building query objects in Access and then building more query objects that use the earlier query objects. Just as rpeare described.

I often use Access query builder to assist in building a nested all-in-one statement - although never done one to this degree.

[rpeare]

Exactly as June said, I tend to build the queries I want first, put the 'final product' together then start substituting in SQL statements as necessary if I want to trim down my number of queries. Typically though I keep things separate in case I need to go in and modify any part of it I can track it back to the offending 'segment' of the query pretty easily rather than unwrapping the whole, larger query again.