Access SQL Query: Left Join does not display all Left Table rows, which is a Union Query

Question

I've got two queries and a table that I want to combine:

[Product Demand] (table)
[NA Supply] (query)
[EU Supply] (query)

These three have identical fields:

Date
Region
Product
Quantity

Although, I will say that on the supply queries, I force the Date and Region to be static values, while Product and Quantity are pulled from a table. For example:

SELECT 
  DateValue("12/1/2017") AS [Date], 
  "North America" AS [Region],  <-- Would be "Europe" for [EU Supply]
  [NA Inv Report].[Product], 
  [NA Inv Report].[Quantity]
FROM [NA Inv Report]   <-- Would be "EU Inv Report" for [EU Supply]
  WHERE ((([NA Inv Report].[Quantity])>0));

I then have a basic Union Query, called [uQuery: Combo]:

SELECT 
  [Product Demand].[Date] AS [Date],
  [Product Demand].[Region] AS [Region],
  [Product Demand].[Product] AS [Product]
FROM [Product Demand]
UNION SELECT 
  [NA Supply].[Date] AS [Date],
  [NA Supply].[Region] AS [Region],
  [NA Supply].[Product] AS [Product]
FROM [NA Supply]
UNION SELECT 
  [EU Supply].[Date] AS [Date],
  [EU Supply].[Region] AS [Region],
  [EU Supply].[Product] AS [Product]
FROM [EU Supply];

Then, I pull that Union Query into a new query;

SELECT 
  [uQuery: Combo].Date, 
  [uQuery: Combo].Region, 
  [uQuery: Combo].Product
FROM [uQuery: Combo];

This shows everything!

But here's the confusing thing; if I LEFT JOIN Product Demand, it still shows everything. BUT if I LEFT JOIN one of NA Supply or EU Supply, then it will filter the results to the added table. If I add BOTH supply tables, then it won't show any results.

SELECT 
  [uQuery: Combo].Date, 
  [uQuery: Combo].Region, 
  [uQuery: Combo].Product
FROM (([uQuery: Combo] 
  LEFT JOIN [Product Demand] ON  <-- Adding just this will show everything
    ([uQuery: Combo].Product = [Product Demand].Product) AND 
    ([uQuery: Combo].Region = [Product Demand].Region) AND 
    ([uQuery: Combo].Date = [Product Demand].Date)) 
  LEFT JOIN [NA Supply] ON  <-- Adding this will only show matching results for [NA Supply]
    ([uQuery: Combo].Product = [NA Supply].Product) AND 
    ([uQuery: Combo].Region = [NA Supply].Region) AND 
    ([uQuery: Combo].Date = [NA Supply].Date)) 
  LEFT JOIN [EU Supply] ON  <-- Adding this will hide all results
    ([uQuery: Combo].Product = [EU Supply].Product) AND 
    ([uQuery: Combo].Region = [EU Supply].Region) AND 
    ([uQuery: Combo].Date = [EU Supply].Date);

[Difference between Union and Union All](https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) — Darren Bartrup-Cook, Dec 28 '17 at 17:04
@StevenBENET This does not solve the issue. I should also note that I have a near identical Union/Left-Join combo for another group of tables, and it works flawlessly without `UNION ALL SELECT`. To clarify, my Union query does show everything. It's only when I use it as the foundation of my LEFT JOIN query does it start hiding results. — MrMusAddict, Dec 28 '17 at 17:09
Why are you self-joining the same queries to themselves in union query? What is your end result? This echoes the [XY Problem](https://meta.stackexchange.com/q/66377) where you want help with a Y solution but are not telling us the X problem and Y may not be needed at all for X. — Parfait, Dec 28 '17 at 17:47
@Parfait I have 3 separate reports that I want to combine; Demand (which includes NA and EU), then NA supply and EU supply. The idea I had was to use a Union query to get all possible Date/Region/Product combos, so that I can use that as a foundation to show me the summary of Supply/Demand per every Date/Region/Product. — MrMusAddict, Dec 28 '17 at 17:50
You are not clearly explaining what you want. What do "filter the results to the added table" & "add BOTH supply tables" mean? You spend a lot of time just using words to redundantly describe what your code does. Then you unclearly say something about how if you do some things then you don't get the undescribed things you expect. Read a definition of left join--it returns what inner join does plus unmatched left table rows extended by nulls. Then edit your question to clearly give your question. (Edit clarifications into posts, don't put them into comments.) Also read & act on [mcve]. — philipxy, Dec 30 '17 at 05:25

score 1 · Answer 1 · answered Dec 28 '17 at 18:20

All results disappear because you are forcing NA and EU Regions to match which they never will as you hard code them to be different values. Even with a LEFT JOIN, NA Supply will not return any rows as logically "North America" <> "Europe".

Consider separating out the Demand and Supply data and then join in final query where matches are potential between demand and supply regions.

Union Query

SELECT 
  [NA Supply].[Date] AS [Date],
  [NA Supply].[Region] AS [Region],
  [NA Supply].[Product] AS [Product]
FROM [NA Supply]
UNION 
SELECT 
  [EU Supply].[Date] AS [Date],
  [EU Supply].[Region] AS [Region],
  [EU Supply].[Product] AS [Product]
FROM [EU Supply]

Final Query

SELECT 
  u.[Date], 
  u.Region, 
  u.Product,
  u.Quantity AS SupplyQty,
  p.Quantity AS DemandQty
FROM [uQuery: Combo] u
LEFT JOIN [Product Demand] p ON
  u.[Date] = p.[Date] AND 
  u.Region = p.Region AND 
  u.Product = p.Product

Full Outer Join Query

Now, you may want a full outer join to include demand without matching supply. And because FULL OUTER JOIN is not directly supported in MS Access, you can achieve equivalent results by union of a RIGHT JOIN (assuming no duplicate rows exist in either table).

SELECT u.[Date], u.Region, u.Product, u.Quantity As SupplyQty, p.Quantity As DemandQty
FROM [uQuery: Combo] u
LEFT JOIN [Product Demand] p ON
  u.[Date] = p.[Date] AND u.Region = p.Region AND u.Product = p.Product

UNION

SELECT u.[Date], u.Region, u.Product, u.Quantity, p.Quantity
FROM [uQuery: Combo] u
RIGHT JOIN [Product Demand] p ON
  u.[Date] = p.[Date] AND u.Region = p.Region AND u.Product = p.Product

ASIDE: Ideally, you want to maintain one Supply source or one Inv Report as UNION can have performance issues especially used in other queries like proposed full outer join that uses UNION.

In database schema, you do not want to duplicate structures for different categories like NA or EU but keep similar data in same object with indicator fields where data values vary. Database tables are not spreadsheet tabs but normalized, related logical data groupings that avoid redundancy.

Access SQL Query: Left Join does not display all Left Table rows, which is a Union Query

1 Answers1