List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 22 1999 4:40pm
Subject:Re: sub-select
View as plain text  
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)
Thread
sub-selectMichael Farr22 Jul
  • Re: sub-selectSasha Pachev22 Jul
  • Re: sub-selectChristian Mack26 Jul