List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 26 1999 6:41pm
Subject:Re: sub-select
View as plain text  
Sasha Pachev wrote:
> 
> 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

Hi Sasha

No, there is no way to do this in one query, because Mike needs two different groupings to
get his result values.

So he has to use your suggestion.

Tschau
Christian

Thread
sub-selectMichael Farr22 Jul
  • Re: sub-selectSasha Pachev22 Jul
  • Re: sub-selectChristian Mack26 Jul