Joining the same table twice in one recordset under MySQL

[Syngin]

I’m trying to join 2 instances of the same table with separate criteria and am running into some serious problems. Essentially, it’s a comparison of an invoice table based on different time criteria. A boiled down version of the table looks like this:

Table name: Invoices

inv_number (int)
inv_date (varchar year, month and day ie. 20050428)
inv_amount (decimal 8,2)
inv_company (varchar ie. not a foreign key)
inv_custnum (int customer number)
sales_rep (int ie. ID of the sales person who set up the transaction)

This table is a dump from DB2 and it wants to force me to use only int and varchar (no clue why) There is no normalization either (which is annoying)

Basically, I want to display a table that compares different months and performs a couple of comparison calculation on a per line basis (I have these calculations set up code side so they aren’t needed in the query. I want to group

The columns in the table I want to display are in this order:

Company, Sales Total for Month 1, Sales Total For Month 2

There are 3 variables to be used in the query: sales_rep (because this page is a report for a particular sales rep grouped by company), date1 and date2. The date variables are posted in the form of year and month ie. 200504)

I’ve used the following query to successfully pull results for Month 1 but, try as I might, I can’t join Month 2 successfully:

SELECT a.inv_company,
a.inv_custnum
a.inv_date
a.sales_rep,
SUM(a.inv_amount) AS totalsales,
a.inv_number
FROM invoices AS a
WHERE a.sales_rep = 'colname' AND a.inv_date LIKE 'colname2%'
GROUP BY a.inv_custnum
ORDER BY a.inv_custnum ASC

(NOTE: colname = $_POST[‘sales_rep’]
colname2 = $_POST[‘date1’])
Does anyone have any idea how I can join the results of a second instance of this table into this query (say based on colname3 = $_POST[‘date2’] ) ? I’ve been pulling my hair out over this for a few days now. About the only thing I haven’t tried is UNION as I’m using MySQL 3.23 and UNION wasn’t supported until 4.0.

Thanks guys

 

[ericbrunson]

Simply name the table twice in the query with different aliases.

For example, given the following:

create table emp (
 id int,
 name varchar(255),
 mgr_id int,
 primary key ( id ),
 foreign key ( mgr_id ) references emp ( id ) );

To join the table to itself:

select a.name empname, b.name mgrname
from   emp a, emp b
where  b.id = a.mgr_id;

 

[Syngin]

Nope, not that easy. I've tried all manner of joins without luck (by this I mean I may get sales figures but they aren't correct) Left Joins, Inner Joins, Right Joins, Prayer Joins etc. nothing correct hehe

 

[ericbrunson]

I didn't read your original post very closely, sorry.

You don't want to join the table with itself, you want to do a conditional summation. Something like:

select blah, blah, blah, 
       sum( case when inv_date = '200504' 
            then inv_amount
            else 0 
            end case ) as AprSales,
       sum( case when inv_date = '200505' 
            then inv_amount
            else 0 
            end case ) as MaySales
where  ...
group by whatever;

That case command is very useful

 

[Syngin]

Hmm, this looks like it might be the way to go. After running an addapted version of this query, I run into a syntax problem:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'case ) as sales1,
sum( case when c.YRMO LIKE '200504%'

Any ideas? This is on MySQL 3.23 which (according to mysql's site) should support CASE. Thanks for your help by the way. Much appreciated. ;-)

 

[ericbrunson]

Always try to isolate the problem to narrow down the suspects. Try writing a simple query using just the 'case'. Also, don't assume what I posted was syntactically correct.

FWIW, the migration from 3.23 to 4.1 is completely painless, you should consider it. If you have a system that supports RPM it's a breeze, just install the RPMs from Mysql and it's done, all the migration scripts are run for you.

 

[r937]

ok --> case ... end

not ok --> case ... end case


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Syngin]

Hello again Rudy,

Wow you're just everywhere.

Cheers