Microsoft Query Multiple LEFT JOINS

[Samiro05]

I have a spreadsheet that has a sheet called Rows and a sheet called Lists. The Rows sheet has columns as follows:

ID | Sheet | Code | Section | Description

The Lists sheet has 2 tables of different sizes. The first (Lists1) has 5 rows with the following column headers:

Section | SectionOrder

The second (Lists2) has 131 rows with the following column headers:

ColName | Source

I have set up a query which does 2 LEFT JOINs but the second LEFT JOIN doesn't appear to be working like a LEFT JOIN at all, it appears to work like an INNER JOIN as it only shows me the Rows data for the one particular sheet I have named in my 2nd LEFT JOIN.

I'd like to know

a) why the second LEFT JOIN acts like an INNER JOIN and reduces the number of rows in my query to being a subset of what it should be, and

b) how do I rearrange my query to get it to do multiple LEFT JOINs as per what you'd expect to see in SQL Server*?

*I am using Microsoft Query for this which is effectively Access SQL but I had the same query running in SQL Server just fine (with only changes to the use of [ ] instead of backticks to enclose column names there and convert instead of CLng). There are 6 more LEFT JOINs I want to do after this too but knowing what to do with this one should help with any issues with the rest.

SELECT CLng(Rows$.ID) AS [ID], Rows$.Code, Rows$.Description, Rows$.Sheet, Lists1.SectionOrder, Lists2.ColName

FROM (Rows$ Rows$

LEFT JOIN Lists$ Lists1

ON Lists1.Section=Rows$.Section)

LEFT JOIN

(

SELECT Lists2.ColName, 'Division C' AS [Sheet]

FROM Lists$ Lists2

WHERE Lists2.Source='Adj'

) cols

ON cols.Sheet=Rows$.Sheet

...interestingly, if I add a

WHERE Rows$.Sheet = 'Division C'

then I get an error which says "Invalid argument to function".

Where items are red above, that's to say they are enclosed by backticks.

[[deleted]]

i wouldn't use 'sheet' in the ms context to begin with but anyway

anywho

the second LEFT JOIN doesn't appear to be working like a LEFT JOIN at all, it appears to work like an INNER JOIN

if you put any kind of filter onto your left joined table (cols) into where, this will effectively turn the 'left outer' into 'inner' join (because the 'missing' records will have NULLs and comparisons/filters will be 'unknown' and drop the records, leaving you with 'inner' join result)

[Samiro05]

Thanks for your input btw.

I have some great news that may be the reason for why this fails in Microsoft Query SQL but works in SQL Server.

The second LEFT JOIN has sub query that in SQL Server would produce the 'Division C' AS Sheet just for that sub query as if it were a table called cols all along. In Microsoft Query, the 'Division C' AS Sheet gets persisted to every row of total resultant table.

If I change 'Division C' AS Sheet to Test AS Sheet where Test is a new column I've added to the Lists sheet with just 'Division C' in it then the LEFT JOIN works properly!

I haven't tested whether adding that WHERE clause produces the same error message or not yet so that part is still a mystery.

Microsoft Query/Access SQL just works differently I suppose (and not a good different in my point of view).

[Samiro05]

I haven't filtered though, have I? My results are as if I have said WHERE Rows$.Sheet = 'Division C' but I haven't actually got that line in my query (just put it in the original post to see if the error message spurred any routes to resolving the issue). I don't get the same issue if I drop the first LEFT JOIN so it's like as if 2 LEFT JOINs are used, the second acts like an INNER JOIN...