Error with Unions

[tdangel]

I'm having an issue with setting up a union query where I receive an error saying "Syntax error in query expression ''." I'm able to run all combinations of the union with just two of the queries, but when I add the third I run into this issue. I also have other queries in this database that have three parts that run with no issues. Below is the query I have right now.

SELECT br2.Branch, cwd.CWDepartment
FROM ((Employees e
LEFT JOIN Branches br ON e.FirstBranch = br.BranchID)
LEFT JOIN Branches br2 ON br.Overrides.Value = br2.BranchID)
LEFT JOIN CWDepartments cwd ON e.CWDepartment = cwd.CWDepartmentID
WHERE br.OverrideBranch = True

UNION

SELECT br2.Branch, cwd.CWDepartment
FROM ((Employees e
LEFT JOIN Branches br ON e.SecondBranch = br.BranchID)
LEFT JOIN Branches br2 ON br.Overrides.Value = br2.BranchID)
LEFT JOIN CWDepartments cwd ON e.CWDepartment = cwd.CWDepartmentID
WHERE (br.OverrideBranch = True) AND (e.SecondBranch IS NOT NULL)

UNION

SELECT br2.Branch, cwd.CWDepartment
FROM ((Employees e
LEFT JOIN Branches br ON e.ThirdBranch = br.BranchID)
LEFT JOIN Branches br2 ON br.Overrides.Value = br2.BranchID)
LEFT JOIN CWDepartments cwd ON e.CWDepartment = cwd.CWDepartmentID
WHERE (br.OverrideBranch = True) AND (e.ThirdBranch IS NOT NULL)

For some additional detail, First, Second, and ThirdBranch all reference the same field from Branches and Overrides is a multiple-value field that I'm trying to get the values from and view all combinations of them and CWDepartment for Employees.

 

[plog]

LEFT JOIN Branches br...
...WHERE (br.OverrideBranch = True)

1. When you add criteria to a field from a LEFT JOIN datasource you undo the LEFT JOIN and logically turn it into a INNER JOIN. You are not showing all records from br but only those where that field is TRUE.

2. Only SELECT, FROM and UNION in a UNION. Don't JOIN, don't WHERE, don't GROUP BY. So much can go wrong in a UNION query just by the UNION itslef it makes it hard to track down the issue when you do other things. So don't do those other things. Instead for each sub query make an actualy query object for them. Take your first SELECT and make a query on it (sub1), for the second SELECT make a query (sub2) for the third make a query (sub3). After those 3 queries are made, run them individually to make sure they work, then make a 4th query to UNION them:

 SELECT Branch, CWDepartment FROM sub1
UNION SELECT Branch, CWDepartment FROM sub2
UNION SELECT Branch, CWDepartment FROM sub3

That makes debugging issues like this a lot easier

 

[ebs17]

e.FirstBranch, e.SecondBranch, e.ThirdBranch

To a large extent, UNION queries are only necessary because some normalization steps are missing in the data modeling.

but when I add the third I run into this issue

Have you tested the query part on its own? UNION is innocent here.

 

[Pat Hartman]

Looks to me like a union is not needed. Why not just a more complex where clause that has all three conditions?

But having three branch values in a single record is generally wrong and should be normalized into a child table.

 

[tdangel]

Going off of Pat Harman's recommendation for using a more complex where clause, I found a solution by making a Cartesian join for all possible Branch and CWDepartments and set where conditions to only show the combinations that are present in the Employees table. I then made a separate query for the Override Branches and unioned that on to the original.

 

[Pat Hartman]

I found a solution by making a Cartesian join

That wasn't necessary at all. All you needed was:

WHERE (br.OverrideBranch = True)
OR (br.OverrideBranch = True AND e.SecondBranch IS NOT NULL)
OR (br.OverrideBranch = True AND e.ThirdBranch IS NOT NULL)

But it is far more important to fix the schema.

 

[Josef P.]

For the sake of interest: would the following select statement produce the same result as the union variant?

SELECT br2.Branch, cwd.CWDepartment
FROM ((Employees e
LEFT JOIN Branches br ON (
e.FirstBranch = br.BranchID OR  e.SecondBranch = br.BranchID OR e.ThirdBranch = br.BranchID)
)
LEFT JOIN Branches br2 ON br.Overrides.Value = br2.BranchID)
LEFT JOIN CWDepartments cwd ON e.CWDepartment = cwd.CWDepartmentID
WHERE br.OverrideBranch = True