Left joining a nested select statement utilizing 3 tables
<p>I'm not sure of a better way to resolve this issue with three tables.  Perhaps there is a function I can use?<br>
CustID is the main primary key and I need to join the sales data from Table3. The sales data should be attached to the table according to CustID.  The problem is that the sales data from table3 does not have CustID info and only serialnumber and sale information.</p>

<h2>How can I utilize left join and properly attach what I need?</h2>

<p>Side questions:</p>

<p>I have a lapse of understanding as to when I can call data to be cross referenced.  </p>

<p>1.)  Can I reference numid outside of the nested select statement later in the query even though it was coded within a nested select statement?</p>

<p>2.)  Can I make mysql reference data in a where statement even though I did not pull a column of data in the select statement? (I reference the table in the from statement though).</p>

<p>3.)  If I rename table2 in my from statement to be "tb2".  In the future within a nested select statement, would I still refer to table2 as "tb2"?</p>

<h2>Thank you for your help.</h2>

<pre><code> Table1
 CustID    serialnumber
   1           1261
   2           6337
   3           1412
   4           6128
   5           1231
 Table2
 CustID    Address   Name    Joindate
   1        xxxx     xxx     xx/xx/xxx
   2        xxxx     xxx     xx/xx/xxx
   3        xxxx     xxx     xx/xx/xxx
 Table3    
 SerialNumber  Purchasedate     email      Sale
      1261      xx/xx/xxxx     xx@xx.com   $20
      1261      xx/xx/xxxx     xx@xx.com   $30
      1261      xx/xx/xxxx     xx@xx.com   $5.99
      1261      xx/xx/xxxx     xx@xx.com   $5.00
      6337      xx/xx/xxxx     xx@xx.com   $72
      1412      xx/xx/xxxx     xx@xx.com   $5.00 
      1412      xx/xx/xxxx     xx@xx.com   $5.00
      1412      xx/xx/xxxx     xx@xx.com   $5.00
      6128      xx/xx/xxxx     xx@xx.com   $5.00
      1231      xx/xx/xxxx     xx@xx.com   $5.00
      1261      xx/xx/xxxx     xx@xx.com   $5.00
select * from Table2 
left join
(select 
Table1.serialnumber as "num", 
Table1.CustID as "numid",
Table3.purchasedate,
Table3.email, 
sum(Table3.Sale)
from 
Table3, Table1 
where Purchasedate between 
date(xx/xx/xxxx) and date(xx/xx/xxxx) 
and Table3.SerialNumber = num)) tblxxx on CustID using numid)
where joindate between date(xx/xx/xxxx) and date(xx/xx/xxxx);
</code></pre>

<p>My expected results would be as follows:</p>

<pre><code> CustID  Address   Name    Joindate  num   numid   purchasedate  email  Sale
   1      xxxx     xxx     xx/xx/xx  1261    1       xx/xx/xx   x@x.com  $390
   2      xxxx     xxx     xx/xx/xx  6337    2       xx/xx/xx   x@x.com  $72
   3      xxxx     xxx     xx/xx/xx  1412    3       xx/xx/xx   x@x.com  $15
</code></pre>