Multiple Left Join Subqueries issue 1

[Aurillius]

Hi everyone,
I have a query which left joins to 3 subqueries containing filters. The syntax looks fine but I keep getting an missing operator on the last expression.

[MAIN PARTS].BULKROOT = 2004SALES.BULKROOT;

Here is the statement:

SELECT *
FROM
(([MAIN PARTS] LEFT JOIN (SELECT [MAIN PARTS].BULKROOT, SALES.YTD
FROM (SALES INNER JOIN REGIONS ON SALES.REGION = REGIONS.REGION_ABV) INNER JOIN [MAIN PARTS] ON SALES.[ITEM-ID] = [MAIN PARTS].PART_NUM
WHERE (((SALES.MONTH)="0608") AND ((REGIONS.REGION)="ALBERTA"))
GROUP BY [MAIN PARTS].BULKROOT, SALES.YTD) AS CURRENTSALES ON [MAIN PARTS].BULKROOT = CURRENTSALES.BULKROOT) LEFT JOIN (SELECT [MAIN PARTS].BULKROOT, SALES.YTD
FROM (SALES INNER JOIN REGIONS ON SALES.REGION = REGIONS.REGION_ABV) INNER JOIN [MAIN PARTS] ON SALES.[ITEM-ID] = [MAIN PARTS].PART_NUM
WHERE (((SALES.MONTH)="0512") AND ((REGIONS.REGION)="ALBERTA"))
GROUP BY [MAIN PARTS].BULKROOT, SALES.YTD) AS 2005SALES ON [MAIN PARTS].BULKROOT = 2005SALES.BULKROOT) LEFT JOIN (SELECT [MAIN PARTS].BULKROOT, SALES.YTD
FROM (SALES INNER JOIN REGIONS ON SALES.REGION = REGIONS.REGION_ABV) INNER JOIN [MAIN PARTS] ON SALES.[ITEM-ID] = [MAIN PARTS].PART_NUM
WHERE (((SALES.MONTH)="0412") AND ((REGIONS.REGION)="ALBERTA"))
GROUP BY [MAIN PARTS].BULKROOT, SALES.YTD) AS 2004SALES ON [MAIN PARTS].BULKROOT = 2004SALES.BULKROOT;

Hope someone can help...thanks.
Mark

 

[Remou]

While this may or may not work, it does not have a syntax error.
[tt]SELECT *
FROM ((([MAIN PARTS] A
LEFT JOIN
(SELECT B.BULKROOT, SALES.YTD
FROM (SALES
INNER JOIN
REGIONS
ON SALES.REGION = REGIONS.REGION_ABV)
INNER JOIN
[MAIN PARTS] B
ON SALES.[ITEM-ID] = B.PART_NUM
WHERE (((SALES.MONTH)="0608") AND ((REGIONS.REGION)="ALBERTA"))
GROUP BY B.BULKROOT, SALES.YTD)
AS C
ON A.BULKROOT = C.BULKROOT)


LEFT JOIN
(SELECT B.BULKROOT, SALES.YTD
FROM (SALES
INNER JOIN
REGIONS
ON SALES.REGION = REGIONS.REGION_ABV)
INNER JOIN
[MAIN PARTS] B
ON SALES.[ITEM-ID] = B.PART_NUM
WHERE (((SALES.MONTH)="0512") AND ((REGIONS.REGION)="ALBERTA"))
GROUP BY B.BULKROOT, SALES.YTD)
AS D
ON A.BULKROOT = D.BULKROOT)

LEFT JOIN
(SELECT B.BULKROOT, SALES.YTD
FROM (SALES
INNER JOIN
REGIONS
ON SALES.REGION = REGIONS.REGION_ABV)
INNER JOIN
[MAIN PARTS] B
ON SALES.[ITEM-ID] = B.PART_NUM
WHERE (((SALES.MONTH)="0412") AND ((REGIONS.REGION)="ALBERTA"))
GROUP BY B.BULKROOT, SALES.YTD)
AS E
ON A.BULKROOT = E.BULKROOT)[/tt]

 

[Aurillius]

Thank you very much! Yours worked.

Mark

 

[Aurillius]

I'm trying to figure why I need declare my left table as "A" and why I can't rename "A, C, D, E" as full names like "MAIN", "2004SALES" etc....

 

[lespaul]

remou has used aliases for the table names:

SELECT *
FROM ((([MAIN PARTS] [b]A[/b]
LEFT JOIN
       (SELECT B.BULKROOT, SALES.YTD
        FROM (SALES
                 INNER JOIN
                 REGIONS
                 ON SALES.REGION = REGIONS.REGION_ABV)
         INNER JOIN
         [MAIN PARTS]  [b]B[/b]
        ON SALES.[ITEM-ID] = B.PART_NUM
        WHERE (((SALES.MONTH)="0608") AND ((REGIONS.REGION)="ALBERTA"))
        GROUP BY B.BULKROOT, SALES.YTD)
AS [b]C[/b]
ON A.BULKROOT = C.BULKROOT)


LEFT JOIN
       (SELECT B.BULKROOT, SALES.YTD
        FROM (SALES
                 INNER JOIN
                 REGIONS
                 ON SALES.REGION = REGIONS.REGION_ABV)
         INNER JOIN
         [MAIN PARTS]  [b]B[/b]
        ON SALES.[ITEM-ID] = B.PART_NUM   
        WHERE (((SALES.MONTH)="0512") AND ((REGIONS.REGION)="ALBERTA"))
        GROUP BY B.BULKROOT, SALES.YTD)
AS [b]D[/b]   
ON A.BULKROOT = D.BULKROOT)

LEFT JOIN
       (SELECT B.BULKROOT, SALES.YTD
        FROM (SALES
                 INNER JOIN
                 REGIONS
                 ON SALES.REGION = REGIONS.REGION_ABV)
         INNER JOIN
         [MAIN PARTS]  [b]B[/b]
        ON SALES.[ITEM-ID] = B.PART_NUM   
        WHERE (((SALES.MONTH)="0412") AND ((REGIONS.REGION)="ALBERTA"))
        GROUP BY B.BULKROOT, SALES.YTD)
AS [b]E[/b]   
ON A.BULKROOT = E.BULKROOT)

it makes the code easier to read. Some of these you have to use an alias. Like D, D is an alias for the entire subquery:

 (SELECT B.BULKROOT, SALES.YTD
        FROM (SALES
                 INNER JOIN
                 REGIONS
                 ON SALES.REGION = REGIONS.REGION_ABV)
         INNER JOIN
         [MAIN PARTS]  [b]B[/b]
        ON SALES.[ITEM-ID] = B.PART_NUM   
        WHERE (((SALES.MONTH)="0512") AND ((REGIONS.REGION)="ALBERTA"))
        GROUP BY B.BULKROOT, SALES.YTD)

which is a selected join statement (meaning that this subquery is the basis for the join) and you HAVE to use an alias for this.

The alias B could be changed, but it's part of the correlation query that correlates the main query to the part of the subquery.

What are you trying to change and why?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Aurillius]

I may have phrased the question wrong.

I understand that it's an alias but the first alias "A" is only a table...C, D and E are subqueries, and yes they are needed. The second part of my question is why can I only use "A" as an alias and not a string like "BANANAS".

 

[Remou]

As far as I know, you can have Bananas as long as it's Bananas everywhere, but I find A, B etc much easier than fruit to sort out.