possiblility of reffering to the reslut of query with another query

bjozefiak · #1 (**permalink**) 11-23-04, 07:25

Hi,
how are u?? i'm beginner and need Your help.

I am asking about possiblility of reffering to the reslut of query below with another query. Result of the query below is the column with numbers of visits at each of the doctors.

select doctor.id_doctor,concat(doctor.name,' ',dostor.surname) as sur_name, count(reg.id_position) as number_of_positions
from reg,doctor
where doctor.id=reg.id_doctor and reg.data_of_visit between '2003-11-20' and '2003-11-21'
group by reg.id_doctor

The purpose of new query is matching the mostly visited docot's name.

Thank You for every advice,
blazej

guelphdad · #2 (**permalink**) 11-23-04, 07:31

So your result is only one row with the doctor who was visited most between those dates?

What version of mysql are you running?

bjozefiak · #3 (**permalink**) 11-23-04, 07:49

My result are rows in number of all doctors in database. Each row includes the columns with name and number of visits to each doctor, which is the result of my query.

I run WinMySQLadmin 1.4.

guelphdad · #4 (**permalink**) 11-23-04, 14:36

windmysqladmin does not tell me what version of mysql it is. if it is 4.1 or newer then you can do sub queries. if it is older then you'd have to do a join.

Show a few rows from both tables and then show what you are asking for by posting some results in the joined table.

r937 · #5 (**permalink**) 11-23-04, 19:03

to find out which version you're on, run this query --

Code:

select version()

bjozefiak · #6 (**permalink**) 11-24-04, 12:58

i work on 4.0.17-nt

r937 · #7 (**permalink**) 11-24-04, 13:13

as far as your original problem is concerned (the "mostly visited" doctor), you can simply sort the result from the query you currently have, and use LIMIT --

Code:

select doctor.id_doctor
     , concat(doctor.name,' ',dostor.surname) 
             as sur_name
     , count(reg.id_position) as number_of_positions
  from doctor
left outer
  join reg
    on doctor.id
     = reg.id_doctor
   and reg.data_of_visit between '2003-11-20' and '2003-11-21' 
group 
    by doctor.id_doctor
     , concat(doctor.name,' ',dostor.surname) 
order 
    by number_of_positions  desc      
limit 1

note i've changed it to a LEFT OUTER JOIN in case there are any doctors without regs, although in this particular query it wouldn't really make much difference, because you want the highest number anyway

Internet Services

Web Development

Digital Marketing

Consumer Tech