Left Join, Inner Join Nested Query

[Guest]

I need a query that shows me all records both assigned and unassigned.
The unassigned would simply just have empty fields.

SELECT AssignedBatches.StartDate, MasterBatch.BatchNumber
FROM MasterBatch LEFT JOIN AssignedBatches ON MasterBatch.BatchNum =
AssignedBatches.BatchNum;

This shows me all the batch numbers. But as soon as I link another table to
it to show more related data, I get an Access error saying there are
ambigious joins and I need to create a seperate query first. I've assumed
this means I need to do a nested join, but I can't seem to get the syntax
correct.

Here's an example of how I think it should work with a field added from the
Employee table:

SELECT Employee.UserId
FROM Employee INNER JOIN (Select MasterBatch.BatchNumber,
AssignedBAtches.StartDate FROM MasterBatch LEFT JOIN AssignedBatches ON
MasterBatch.BatchNum = AssignedBatches.BatchNum) ON Employee.EmpNum =
AssignedBatches.EmpNum;

I've read that a Left Join can't be on the outside of the nested
statement...is that true?

Please help!

 

[Armen Stein]

I need a query that shows me all records both assigned and unassigned.
The unassigned would simply just have empty fields.

SELECT AssignedBatches.StartDate, MasterBatch.BatchNumber
FROM MasterBatch LEFT JOIN AssignedBatches ON MasterBatch.BatchNum =
AssignedBatches.BatchNum;

This shows me all the batch numbers. But as soon as I link another table to
it to show more related data, I get an Access error saying there are
ambigious joins and I need to create a seperate query first. I've assumed
this means I need to do a nested join, but I can't seem to get the syntax
correct.

Once you have an outer join going, you can't continue "outward" with an
inner join - you need to keep going with outer joins.

Here's my extremely simplified way of avoiding the dreaded "ambiguous
outer joins" error:

1. All join arrows must point away from the "central" table of your
query.
2. The "central" table of your query may be just one table, or it may
consist of multiple tables connected with inner joins (no arrowheads).

 

[John Spencer]

How about the following? Does it work for you?

SELECT AssignedBatches.StartDate
, MasterBatch.BatchNumber
, Employee.UserID
FROM (MasterBatch LEFT JOIN AssignedBatches
ON MasterBatch.BatchNum = AssignedBatches.BatchNum)
LEFT JOIN Employee ON AssignedBatches.EmpNum = Employee.EmpNum

Note that Access requires the inclusion of parentheses in the FROM clause
when you are joining more than two tables.

 

[Guest]

Thank you guys! It works and I have a better understanding now.

You rock!