Angel02
asked on
Join expression not supported when using LEFT JOIN
I am running the following query on table1 using MS access. The last column needs 2 LEFT JOINS on table2 and table3. If table3.boxes > 0, last column should be 1. If table3.boxes <0 or if the matching row does not exist in table3, last column should be 1.
The following query works.
SELECT Table1.DESCRIPTION, Table1.FARM, Sum(Table1.BOXES) AS QTY, Table1.DATE1 AS [DATE SOLD],Sum(TABLE1.DOLLAR) AS DOLLARS, MAX(IIf((TABLE3.[BOXES] > 0), 1,0)) AS foreca
FROM (Table1 LEFT JOIN TABLE2 ON Table1.Description = TABLE2.Name) LEFT JOIN TABLE3 ON (TABLE2.Product = TABLE3.Product and TABLE3.YEAR= 2015 and TABLE3.WEEK=5)
GROUP BY Table1.DESCRIPTION, Table1.FARM, Table1.DATE1,
ORDER BY Table1.DESCRIPTION
But when I replace the constants with table3 column names, it throws error "join expression not supported"
SELECT Table1.DESCRIPTION, Table1.FARM, Sum(Table1.BOXES) AS QTY, Table1.DATE1 AS [DATE SOLD],Sum(TABLE1.DOLLAR) AS DOLLARS, MAX(IIf((TABLE3.[BOXES] > 0), 1,0)) AS foreca
FROM (Table1 LEFT JOIN TABLE2 ON Table1.Description = TABLE2.Name) LEFT JOIN TABLE3 ON (TABLE2.Product = TABLE3.Product and TABLE3.YEAR= YEAR(table1.DATE1) and TABLE3.WEEK=Weekday(table1
GROUP BY Table1.DESCRIPTION, Table1.FARM, Table1.DATE1,
ORDER BY Table1.DESCRIPTION
The following query works.
SELECT Table1.DESCRIPTION, Table1.FARM, Sum(Table1.BOXES) AS QTY, Table1.DATE1 AS [DATE SOLD],Sum(TABLE1.DOLLAR) AS DOLLARS, MAX(IIf((TABLE3.[BOXES] > 0), 1,0)) AS foreca
FROM (Table1 LEFT JOIN TABLE2 ON Table1.Description = TABLE2.Name) LEFT JOIN TABLE3 ON (TABLE2.Product = TABLE3.Product and TABLE3.YEAR= 2015 and TABLE3.WEEK=5)
GROUP BY Table1.DESCRIPTION, Table1.FARM, Table1.DATE1,
ORDER BY Table1.DESCRIPTION
But when I replace the constants with table3 column names, it throws error "join expression not supported"
SELECT Table1.DESCRIPTION, Table1.FARM, Sum(Table1.BOXES) AS QTY, Table1.DATE1 AS [DATE SOLD],Sum(TABLE1.DOLLAR) AS DOLLARS, MAX(IIf((TABLE3.[BOXES] > 0), 1,0)) AS foreca
FROM (Table1 LEFT JOIN TABLE2 ON Table1.Description = TABLE2.Name) LEFT JOIN TABLE3 ON (TABLE2.Product = TABLE3.Product and TABLE3.YEAR= YEAR(table1.DATE1) and TABLE3.WEEK=Weekday(table1
GROUP BY Table1.DESCRIPTION, Table1.FARM, Table1.DATE1,
ORDER BY Table1.DESCRIPTION
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SELECT Table1.DESCRIPTION, Table1.FARM, Sum(Table1.BOXES) AS QTY, Table1.DATE1 AS [DATE SOLD],Sum(TABLE1.DOLLAR) AS DOLLARS, YEAR(Table1.DATE1) As FCYear, WEEKDAY(Table1.DATE1) As FCWeek,IIf(WEEKDAY(Table1.
FROM Table1
--Saved the above query as Qry_FC_Dual
SELECT FCDaily.* from (Qry_FC_Dual AS FCDaily LEFT JOIN table2 ON FCDaily.Description = table2.Name) LEFT JOIN table3 ON (table2.product = table3.product and table3.YEAR=FCDaily.FCYear
This second query still throws the same error. Join not supported.
Also, I need to run this query from a VB application. I was hoping I could do it in one single query. Please advise.
FROM Table1
--Saved the above query as Qry_FC_Dual
SELECT FCDaily.* from (Qry_FC_Dual AS FCDaily LEFT JOIN table2 ON FCDaily.Description = table2.Name) LEFT JOIN table3 ON (table2.product = table3.product and table3.YEAR=FCDaily.FCYear
This second query still throws the same error. Join not supported.
Also, I need to run this query from a VB application. I was hoping I could do it in one single query. Please advise.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2. I'd suggest joining just table1 and table2 first, including all the columns that you need for the join to table 3, then save this query and use it as a derived table to join to table3.