If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: possiblility of reffering to the reslut of query with another query

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

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