Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    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

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

    Thank You for every advice,
    blazej

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    So your result is only one row with the doctor who was visited most between those dates?

    What version of mysql are you running?

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to find out which version you're on, run this query --
    Code:
    select version()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Posts
    3
    i work on 4.0.17-nt

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •