Here it goes. This is a small schema of the two tables.
Table Employees
i_recid = auto increment UNIQUE KEY
First Name
Last Name
ETC.
Table Contracts
sales_rep = e.i_recid
Contract Date = date of contract
Office = office
So here is what i am trying to do.
Run a report that calulates Who had a sale between adate (2001-04-05) and bdate
(2001-05-05) AND that report can be in any number range specified <= 8 (in other words
less than 8 contracts written) in order of count (7,6,5,4,3,2,1,0). I need it to show
0, that is the main problem. It isn't showing zeros becuase before i was doing a
SELECT directly from the contracts table. The client is insisting on having the ZERO
though and it has to stay in order. Below is a sample SQL query, i have tried a few
others but i am stuck.
<ilist search="SELECT count(c.sales_rep) as cnt, concat(e.last_name,', ',e.first_name)
as full_name, c.office, c.sales_rep, e.i_recid
FROM employee e LEFT JOIN contracts c on e.i_recid=c.sales_rep
WHERE c.install_date='@date(YYYY-NM-ND)' <ieval @input(office,0) ne 0>and
c.office=@input(office)</ieval>
GROUP by c.office, c.sales_rep having count(c.sales_rep) @input(operation)
@input(count,0)
ORDER BY cnt desc">
Ignore the ilist (sql) and ieval (perl's IF THEN) they are a dynamic way of embedding
sql and perl code into the html.
The big problem is that i have been unable to join the tables because of the WHERE
conditions. I am not sure if the subqueries are even supported by mysql. Will
creating a temporary table using a LEFT JOIN to collect the information work? Or is
there an easier way that i have overlooked.
Dan
