Michael Farr wrote:
>
> I need some help with a query, which would probably be handled well with a
> sub-select. Apparently we cant do that yet, but I am hoping there is
> another way around it.
>
> From this query here, I get a table that returns a subset of person
> identification numbers (patientId) from the database
>
> SELECT p.daylyPayment*(TO_DAYS(NOW()) - TO_DAYS(p.treatmentCommenceDate)) +
> p.downPayment AS totalDebit, SUM(pay.credit) AS totalCredit,
> (p.daylyPayment*(TO_DAYS(NOW()) - TO_DAYS(p.treatmentCommenceDate)) +
> p.downPayment - SUM(pay.credit)) AS Owe ,p.patientId, p.daylyPayment FROM
> Payment pay, Patient p WHERE p.patientId = pay.patientId GROUP BY
> p.patientId HAVING Owe > daylyPayment*:daysOut;
>
> +------------+-------------+--------+-----------+--------------+
> | totalDebit | totalCredit | Owe | patientId | daylyPayment |
> +------------+-------------+--------+-----------+--------------+
> | 554.00 | 500.00 | 54.00 | 1 | 6.00 |
> | 295.00 | 40.00 | 255.00 | 2 | 5.00 |
> +------------+-------------+--------+-----------+--------------+
>
> I would like to select the payments from a Payments table and group then
> according to the date they occured, but only for the patients that come up
> in the first query.
>
> mysql> select * from Payment;
> +-----------+-----------+------------+---------------+--------+
> | paymentId | patientId | datePaid | recieptNumber | credit |
> +-----------+-----------+------------+---------------+--------+
> | 1 | 1 | 1999-05-24 | 1002 | 50.00 |
> | 2 | 1 | 1999-06-01 | 1006 | 90.00 |
> | 3 | 1 | 1999-06-14 | 1034 | 120.00 |
> | 4 | 1 | 1999-06-27 | 1048 | 80.00 |
> | 5 | 1 | 1999-07-12 | 1067 | 160.00 |
> | 6 | 2 | 1999-08-12 | 1037 | 40.00 |
> +-----------+-----------+------------+---------------+--------+
> SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
>
> In the help document it says that:
> "For more complicated sub queries you can create temporary tables to hold
> the sub query. "
>
> I am using Borland Builder 4 as my devel tool and need to create account
> reports for all patients in the first table that look like the second
> table. I would rather not process this on the local machine as the link to
> the mysql server can be slow.
>
> Mike
There might be a smart way to do everything in one query, if there is
Christian Mack will figure it out :-)
In the meantime, I would recommend using a temporary table if the
intermediate result set is large, or just processing intermediate
results in the application if it is small. Either way, the slow link to
the server should not present a big problem.
--
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
