Multiple FROM Clauses in VBA code for a single block of code

Why are three FROM clauses needed below..I assume the second FROM clause is needed to build the first ?  If that is the case does the order of the FROM clause matter ?  

Note the below works..I am just trying to understand it....

Select G.Client, G.Date

FROM(Select GROCERY.Client,GROCERY.DATE
FROM (Select LEFT([FullClientName,40) AS Client,Date
FROM strExcelWSName
WHERE [Product] = 'Pizza') AS GROCERY

INNER JOIN (Select LEFT([FullClientName,40) AS Client,Date
FROM strWSName
WHERE [Product] = 'Soybeans' AS AG

ON GROCERY.CLIENT = AG.CLIENT

GROUP BY GROCERY.CLIENT,GROCERY.DATE AS
upobDaPlayaAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
This is a corrected version of the code, and the indentation should help you understand the structure.
SELECT
      G.Client
    , G.DATE
FROM (
            SELECT
                  GROCERY.Client
                , GROCERY.DATE
            FROM (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strExcelWSName
                        WHERE [Product] = 'Pizza'
                  ) AS GROCERY
                  INNER JOIN (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strWSName
                        WHERE [Product] = 'Soybeans'
                  ) AS AG
                        ON GROCERY.CLIENT = AG.CLIENT
      ) AS G
GROUP BY
      GROCERY.CLIENT
    , GROCERY.[DATE]

Open in new window

I assume the second FROM clause is needed to build the first ?  
YES, they are a series of subqueries.
 If that is the case does the order of the FROM clause matter ?  
Does the order matter? YES
0
 
PortletPaulfreelancerCommented:
That code would not work "as is", there are syntax errors
0
 
upobDaPlayaAuthor Commented:
OMG ! The indentation makes a huge difference.  So the hierarchy of the sub-queries then is the one that always appears at the top is the one that gets built last..
0
 
Robberbaron (robr)Connect With a Mentor Commented:
yes to heiracry..

but i think the last 3 lines should be
GROUP BY
      G.CLIENT
    , G.[DATE]

Open in new window


due to the ) AS G  immediately above.

its possible the outer clause is superfluous as well; the group by could be on the second select, in which case the Group By names would be corrent

eg
      SELECT
                  GROCERY.Client
                , GROCERY.DATE
        FROM (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strExcelWSName
                        WHERE [Product] = 'Pizza'
                  ) AS GROCERY
                  INNER JOIN (
                        SELECT
                              LEFT([FullClientName], 40) AS Client
                            , [Date]
                        FROM strWSName
                        WHERE [Product] = 'Soybeans'
                  ) AS AG
                        ON GROCERY.CLIENT = AG.CLIENT
         GROUP BY
              GROCERY.CLIENT
              , GROCERY.[DATE]

Open in new window

0
 
upobDaPlayaAuthor Commented:
Thanks all...makes sense
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.