MySQL

Advanced SQL query problem

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Dec 2008

Posts: 1

Reputation: joliejoker is an unknown quantity at this point

Solved Threads: 0

joliejoker joliejoker is offline

Offline

Newbie Poster

Advanced SQL query problem

Dec 18th, 2008

Hello.
I'm burning my head on a problem with a query.
Querying a payments table, I need to find the actual membership category (let's say: IDCATEGORIA). If I run this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT idpersona, idcategoria, datapagamento, datascadenza
FROM personacategoria 
WHERE NOW( )
BETWEEN datapagamento
AND datascadenza
ORDER BY idpersona, datascadenza
SELECT idpersona, idcategoria, datapagamento, datascadenza
FROM personacategoria 
WHERE NOW( )
BETWEEN datapagamento
AND datascadenza
ORDER BY idpersona, datascadenza

I get this result:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
idPersona  idcategoria  datapagamento  datascadenza
2  2008-12-07  2010-03-31
2  2007-12-17  2009-03-31
1  2008-11-08  2010-03-31
2  2008-12-08  2010-03-31
2  2007-11-11  2009-03-31
2  2008-12-10  2010-03-31
idPersona  idcategoria  datapagamento  datascadenza
2  2008-12-07  2010-03-31
2  2007-12-17  2009-03-31
1  2008-11-08  2010-03-31
2  2008-12-08  2010-03-31
2  2007-11-11  2009-03-31
2  2008-12-10  2010-03-31

The problem, as you can notice, is the second and third row: person 4 paid on the 2007-12-17 its membership, and then again one year later on the 2008-11-08. So, if I query now (2008-12-17), I obviously am in the overlapping period, so that I retrieve two records.
What I want to do is to retrieve, in cases like this, only the oldest DATASCADENZA record (obviously, for each single IDPERSONA).
I've tried lots of things today, but nothing seems to work... any hint?

thanks,
jj

n.b.: (just in case you need it): primary key for the table is IDPERSONA+IDCATEGORIA+DATAPAGAMENTO

•

Join Date: Dec 2007

Posts: 113

Reputation: hielo is on a distinguished road

Solved Threads: 17

hielo

Offline

Junior Poster

Re: Advanced SQL query problem

Dec 21st, 2008

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT idpersona, Max(idcategoria) as categoria, Max(datapagamento) as pagamento, Max(datascadenza) as scadenza 
FROM personacategoria  
WHERE NOW( ) 
BETWEEN datapagamento 
AND datascadenza 
GROUP BY idpersona
ORDER BY idpersona
SELECT idpersona, Max(idcategoria) as categoria, Max(datapagamento) as pagamento, Max(datascadenza) as scadenza 
FROM personacategoria  
WHERE NOW( ) 
BETWEEN datapagamento 
AND datascadenza 
GROUP BY idpersona
ORDER BY idpersona