Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Question Unanswered: How can I select an id that has the lowest value in another table?

    This is what I'm doing

    SELECT * FROM pacient_export WHERE (idpacient_nit IN (SELECT idpacient FROM treatment WHERE date_treatment < CURDATE() - INTERVAL 180 DAY) OR idpacient_nit IN (SELECT idpacient FROM report7)) AND idpacient_nit IN (SELECT idpacient FROM report7 WHERE charge < 50)

    SELECT * FROM pacient_export WHERE (idpacient_nit IN (SELECT idpacient FROM treatment WHERE date_treatment < CURDATE() - INTERVAL 180 DAY) OR idpacient_nit IN (SELECT idpacient FROM report7)) AND idpacient_nit IN (SELECT idpacient FROM report7 WHERE charge >= 50)

    The problem is that in table report7 a pacient can appear several times, it will select the same pacient several times...how can I select the lowest charge value in table report7 for each pacient?

    For example
    table: report7
    pacient | charge
    101 | 32
    101 | 92

    Results:
    pacient | charge
    101 | 32
    THanks!!
    ..::Antonioj1015::..

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Look at SELECT GROUP BY. In your case you would need to issue the following statement:

    SELECT pacient, MIN(charge) AS charge
    FROM pacient_export
    WHERE (idpacient_nit IN (SELECT idpacient FROM treatment WHERE date_treatment < CURDATE() - INTERVAL 180 DAY)
    OR idpacient_nit IN (SELECT idpacient FROM report7)) AND idpacient_nit IN (SELECT idpacient FROM report7 WHERE charge < 50)
    GROUP BY pacient;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

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