Access nested joins with multiple where's possible?
<p>I have this SQL statement which is close to what I want:</p>

<pre><code>SELECT CustomerList.*, Sales.Sales, Sales.MonthYear
FROM (CustomerList INNER JOIN WhoBuysWhat ON CustomerList.CustomerID = WhoBuysWhat.CustomerID) LEFT JOIN Sales ON CustomerList.CustomerID = Sales.CustomerID
WHERE WhoBuysWhat.ProductID=2
ORDER BY CustomerList.CustomerName;
</code></pre>

<p>That gives me all the customers that buy a certain product line and any sales from the Sales table for that product line. But what I want is to add another WHERE statement. I want the first INNER JOIN to give me all the customers that buy productID 2. Then I want to Left Join the sales for ones that match sales.monthyear=#1/1/2015#. So I tried this (in different ways):</p>

<pre><code>SELECT CustomerList.*, Sales.Sales, Sales.MonthYear
FROM ((CustomerList INNER JOIN WhoBuysWhat ON CustomerList.CustomerID = WhoBuysWhat.CustomerID) WHERE WhoBuysWhat.ProductID=2) LEFT JOIN Sales ON CustomerList.CustomerID = Sales.CustomerID
WHERE Sales.MonthYear=#1/1/2015#
ORDER BY CustomerList.CustomerName;
</code></pre>

<p>And that doesn't work. If I do this:</p>

<pre><code>WHERE WhoBuysWhat.ProductID=2 AND Sales.MonthYear=#1/1/2015#
</code></pre>

<p>OR</p>

<pre><code>WHERE Sales.ProductID=2 AND Sales.MonthYear=#1/1/2015#
</code></pre>

<p>It only gives me the customers that have sales in that month. I want ALL customers that buy that line and ANY sales for a GIVEN month. Is that possible in 1 SQL statement? Do I need to use AS for the first joined tables? I cannot find anything that will help me.</p>

<p>J_K_M_A_N</p>

<p>EDIT:
This is the final working SQL statement if anyone cares or needs help later.</p>

<pre><code>SELECT CustomerList.*, Sales.SalesID, Sales.Sales, WhoBuysWhat.EntryID, WhoBuysWhat.Active
FROM (CustomerList INNER JOIN WhoBuysWhat ON CustomerList.CustomerID = WhoBuysWhat.CustomerID)
LEFT JOIN Sales ON (CustomerList.CustomerID = Sales.CustomerID AND Sales.MonthYear=#1/1/2015# AND Sales.ProductID=2)
WHERE WhoBuysWhat.ProductID=2
ORDER BY WhoBuysWhat.Active, CustomerList.CustomerName
</code></pre>