Solved Left Join on Union Query not working

FueledbyAccess

New member
Local time
Today, 15:27
Joined
Sep 6, 2019
Messages
6
Hello,

Hoping some one can educate me on LEFT JOINS a bit. I'm trying to add a LEFT JOIN to a UNION JOIN but when I run the query I receive the following 'Syntax error (Missing Operator) in query expression ‘t0.Project = t3.[Project Code] Left JOIN [Ref_Project Cost Centres] AS t4 on t0.[Task Key’.’

The query works if I remove the LEFT JOIN and also works if I try LEFT JOIN [Ref_Projects & Hierarchy] AS t3 ON t0.Project = t3.[Project Code]; or LEFT JOIN [Ref_Project Cost Centres] AS t4 ON t0.[Task Key] = t4.[Key]; on their own but not together.


Below is the Full Query and attached are extracts of the table being used.

SELECT *

FROM (select iif(t2.Source = 'Committed', 'ForecastCommitted' , 'Actuals' ) as SourceType,

t2.Source, t2.[Nominal Code], '' AS [Date], t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], '' AS Required, t2.[Order Number], '' AS [Order date], t2.Creator, t2.[Line Number], (t2.Net) , (t2.Hours), t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, iif(t2.Source = 'Committed', 'Committed' , 'Actuals' ) as Quality

from Actuals_raw t2

where val(Period) <= (select Period from Last_actuals) and t2.Source = 'Actuals' and Cost_Type1 <> 'Revenue' and t2.Hours = 0

union all select

iif(t2.Source = 'Committed', 'ForecastCommitted' , 'Actuals' ) as SourceType, 'Forecast' as Source, t2.[Nominal Code], '' AS [Date], t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], t2.Required AS Required, t2.[Order Number], '' AS [Order date], t2.Creator, t2.[Line Number], (t2.Net) , (t2.Hours) , t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, iif(t2.Source = 'Committed', 'Committed' , 'Actuals' ) as Quality

from Actuals_raw t2

where t2.Source = 'Committed' and Cost_Type1 <> 'Revenue' and t2.Hours = 0

UNION ALL SELECT t2.Source as SourceType,

t2.Source, t2.[Nominal Code], '' AS [Date], t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], '' AS Required, t2.[Order Number], '' AS [Order date], t2.Creator, t2.[Line Number], Sum(t2.Net) AS SumOfNet, Sum(t2.Hours) AS SumOfHours, t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, t2.[Detail Line] AS Quality

FROM Actuals_raw AS t2

where val(Period) <= (select Period from Last_actuals) and Source = 'Actuals' and Cost_Type1 <> 'Revenue' and t2.Hours <>0

GROUP BY t2.Source, t2.[Nominal Code], '', t2.Month, t2.[Header Ref], t2.[Internal Ref], t2.Supplier, t2.Year, t2.Project, t2.Task, t2.[Task Key], t2.[Detail Line], '', t2.[Order Number], '', t2.Creator, t2.[Line Number], t2.[Nominal Account Name], t2.Cost_Type1, t2.Cost_Type2, t2.Cost_Type3, t2.GroupCostCentre, t2.Period, t2.[Detail Line], Val([Period]), t2.[Source], t2.[Cost_Type1]

UNION ALL SELECT

'ForecastPM' AS Sourcetype, 'Forecast' AS Source,

t1.[Nominal Code], '' AS [Date], t1.Month, t1.[Header Ref], t1.[Internal Ref], t1.Supplier, t1.Year, t1.Project, t1.Task, t1.[Task Key], t1.[Detail Line], '' AS Required, t1.[Order Number], '' AS [Order date], t1.Creator, t1.[Line Number], (t1.Net), (t1.Hours), t1.[Nominal Account Name], t1.Cost_Type1, t1.Cost_Type2, t1.Cost_Type3, t1.GroupCostCentre, t1.Period, t1.[Detail Line] AS Quality

FROM Forecast AS t1

where period > (select Period from Last_actuals)
and Cost_Type1 <> 'Revenue') AS t0

LEFT JOIN [Ref_Projects & Hierarchy] AS t3 ON t0.Project = t3.[Project Code]
LEFT JOIN [Ref_Project Cost Centres] AS t4 ON t0.[Task Key] = t4.[Key];
 

Attachments

you are better off with Union query.
it's because you don't have Unique key to your project field.
 
Eat the elephant in bites not one big gulp. You essentially have a bunch of individual queries, get each to work by itself as you want, then UNION them. Don't cram a ton of logic and the UNION together, you'll never find out exactly where its going wrong and its a pain to debug.

So, make the first SELECT its own query (sub1). Then make the second SELECT its own query (sub2), etc, etc.. Debug each as needed. Then make a last query that just UNIONs them together:

SELECT * FROM sub1
UNION ALL
SELECT * FROM sub2
UNION ALL
SELECT * FROM sub3
....
 
Hi. Welcome to AWF!

Is the problem only about getting the syntax error? If so, I would also suggest starting with a simpler SQL statement first, so you can get the LEFT JOINs working. After that, you can start adding more fields and criteria.

Just a thought...
 
Hi all, thanks for your advice on this one. I went with plog's suggestion of making each select it's own query.
 

Users who are viewing this thread

Back
Top Bottom