MySQL Forums
Forum List  »  MySQL Query Browser

How can I select an id that has the lowest value in another table?
Posted by: Antonio Cardenas
Date: July 16, 2013 09:24AM

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.

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

Results:
pacient | charge
101 | 32

how can I select the lowest charge value in table report7 for each pacient?

THanks!!

Options: ReplyQuote


Subject
Written By
Posted
How can I select an id that has the lowest value in another table?
July 16, 2013 09:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.