List:General Discussion« Previous MessageNext Message »
From:william drescher Date:March 31 2013 11:36am
Subject:Re: Query help -
View as plain text  
On 3/31/2013 7:32 AM, william drescher wrote:
> I have a table, schedule:
> CREATE TABLE `schedule` (
>    `schedule_id` mediumint(9) NOT NULL AUTO_INCREMENT,
>    `provider` varchar(15) NOT NULL,
>    `apptTime` datetime NOT NULL,
>    `location` varchar(10) NOT NULL,
>    `duration` smallint(5) unsigned NOT NULL,
>    `standing_script` mediumint(9) DEFAULT NULL,
>    `appt_status` char(1) NOT NULL,
>    `patient_number` mediumint(9) NOT NULL,
>    `notify` smallint(6) DEFAULT NULL,
>    `comment` varchar(80) DEFAULT NULL,
>    `history` varchar(200) DEFAULT NULL,
>    `posted` tinyint(1) NOT NULL DEFAULT '0',
>    PRIMARY KEY (`schedule_id`),
>    UNIQUE KEY `patient` (`patient_number`,`apptTime`,`schedule_id`),
>    UNIQUE KEY `user` (`user`,`apptTime`,`schedule_id`),
>    KEY `standing` (`standing_script`),
>    KEY `posted` (`posted`,`user`,`apptTime`)
> ) ENGINE=InnoDB  DEFAULT CHARSET=ascii;
>
> all of which can be ignored except for 'provider' and apptTime.
>
> I want to query the database and have the result be only the next
> appointment for  'patient_number' with each user (the doctor).
>
> eg:
> 2013-04-04 JSmith
> 2013-04-20 WJones
>
> where the database contains:
> 2013-04-04 JSmith
> 2013-04-10 JSmith
> 2013-04-17 Jsmith
> 2013-04-20 WJones
> 2013-04-24 JSmith
> etc
>
> I can get a list of future appointments for 1 patient, but can't
> figure out how to just get the first for each provider (there
> might be 1..5 providers)
>
> Any suggestions will be appreciated.
> --bill

This will be a seldom used query and the schedule database is 
relatively small, so overhead is not a big deal.

--bill


Thread
Query help -william drescher31 Mar
  • Re: Query help -william drescher31 Mar
    • Re: Query help - Solvedwilliam drescher31 Mar