Re: Specifics on using join & multiple tables

[Bruce Feist]

Patrick Shoaf wrote:

At 09:48 AM 6/4/2003, Bruce Feist wrote:

I suspect that sales1 and sales2 should really be a single 'sales'
table with an extra column indicating which month the sales are for (and
maybe one for year as well), but I don't have enough information to 
be sure.
Table sales1 & sales2 are tables generated from a larger table 
containing way too much information.  Tables Sales1 contains only 
sales tickets summarized by day for month 1, and Sales2 contains same 
info for month 2.  I needed to generate a sales comparison report for 
1 store with two different months.  Since I did not know how to 
retrieve in 1 select, I generated three, first pull sales info for 
month1 to sales1, then pull sales info for month 2 to sales2, then try 
and combine these to show customers sales and difference between the 
two months.
Ok.  If they're generated tables used only temporarily, it's not really
an issue.  You probably could do it all in a single query, if you'd
prefer, though.  It would be something like:
   SELECT acc.name, acc.phone, acc.acctno,
          sales1.amt AS mo1sales, sales2.amt AS mo2sales,
          (sales1.amt - sales2.amt) AS diff
   FROM acc
     LEFT JOIN sales sales1 USING acctno
     LEFT JOIN sales sales2 USING acctno
  WHERE sales1.month = 1 and sales2.month = 2
If the actual sales table has information more finely broken down than
by month, you'd have to GROUP BY something like this:
   SELECT acc.name, acc.phone, acc.acctno,
          sum(sales1.amt) AS mo1sales, sum(sales2.amt) AS mo2sales,
          sum(sales1.amt - sales2.amt) AS diff
   FROM acc
     LEFT JOIN sales sales1 USING acctno
     LEFT JOIN sales sales2 USING acctno
  WHERE sales1.month = 1 and sales2.month = 2
  GROUP BY acc.name, acc.phone, acc.acctno
Bruce Feist



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]