Edward Ko Edward Ko - 1 month ago 9
MySQL Question

Left joining a nested select statement utilizing 3 tables

I'm not sure of a better way to resolve this issue with three tables. Perhaps there is a function I can use?

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 it by CustID. The problem is that the sales data from table3 does not have CustID info and only serialnumber and sale information.

How can I utilize left join and properly attach what I need?



Side questions:

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

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?

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).

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"?

Thank you for your help.



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);


My expected results would be as follows:

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

Answer
SELECT
    *
FROM
    table2
INNER JOIN table1 ON table1.CustID = table2.CustID
INNER JOIN (
    SELECT
        SerialNumber,
        SUM(Sale) AS sales
    FROM
        table3
    WHERE
        Purchasedate BETWEEN date(xx / xx / xxxx)
    AND date(xx / xx / xxxx)
    GROUP BY
        SerialNumber
) AS table3 ON table3.SerialNumber = table1.serialnumber
WHERE joindate BETWEEN date(xx / xx / xxxx) AND date(xx / xx / xxxx);

Join table1 onto table2 by using the CustID.

Join the SUM of the sales from table3 grouped by the serialnumber onto table1 by using the serial number. By using the WHERE-clause you can specify which sales are to be considered.

Add a 'WHERE'-clause on the outer query.

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).

Yes. The SELECT and WHERE clause are independent of each other. You could even write stuff like 1 = 1 in your WHERE-clause. Also something like SELECT "abcds345234" works.

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"?

You want to give an alias to a table. You have to watch out in what context you are moving when trying to reference it again.

If you are in the same SUBSELECT you use the same alias to reference it. If you have left the SUBSELECT, you have to check to see which is the current name for the table.

Comments