MySQL Forums
Forum List  »  Optimizer & Parser

Query
Posted by: Jorge Ferreira
Date: March 15, 2006 11:08AM

Hi All,

I've got several records inside a table (Table name = registo) and i've got another table (Table name = pagamento) with all the payments that the clients do inside each record of the table registo.
Wich means that each record inside pagamento as only one related record inside the table registo and each record of registo can have several records related in table pagamento.

How can i get all the records inside the table registo and only the last record inside the table pagamento?

Here are the tables

mysql> describe registo
-> ;
+--------------+---------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+------------+----------------+
| id_reg | int(11) | NO | PRI | NULL | auto_increment |
| id_mun | int(10) unsigned | NO | MUL | 0 | |
| prazo | int(10) unsigned | NO | | 0 | |
| tipo_reg | int(10) unsigned | NO | MUL | 0 | |
| montante | float unsigned | YES | | 0 | |
| ovp_1 | tinyint(1) | NO | | 0 | |
| ovp_2 | tinyint(1) | NO | | 0 | |
| ovp_3 | tinyint(1) | NO | | 0 | |
| ovp_4 | tinyint(1) | NO | | 0 | |
| ovp_5 | tinyint(1) | NO | | 0 | |
| ovp_5_desc | varchar(255) | YES | | NULL | |
| ovp_6 | tinyint(1) | NO | | 0 | |
| ovp_7 | tinyint(1) | NO | | 0 | |
| ovp_8 | tinyint(1) | NO | | 0 | |
| ovp_9 | tinyint(1) | NO | | 0 | |
| ovp_10 | tinyint(1) | NO | | 0 | |
| ovp_11 | tinyint(1) | NO | | 0 | |
| ovp_11_desc | varchar(255) | YES | | NULL | |
| pub_1 | tinyint(1) | NO | | 0 | |
| pub_2 | tinyint(1) | NO | | 0 | |
| pub_3 | tinyint(1) | NO | | 0 | |
| pub_4 | tinyint(1) | NO | | 0 | |
| pub_5 | tinyint(1) | NO | | 0 | |
| pub_6 | tinyint(1) | NO | | 0 | |
| pub_7 | tinyint(1) | NO | | 0 | |
| pub_7_desc | varchar(255) | YES | | NULL | |
| pub_8 | tinyint(1) | NO | | 0 | |
| pub_8_desc | varchar(255) | YES | | NULL | |
| localizacao | varchar(255) | YES | | NULL | |
| data_criacao | date | NO | | 0000-00-00 | |
| dimensoes | varchar(255) | YES | | | |
| ovp_7_desc | varchar(255) | YES | | | |
| id_lugar | int(10) unsigned | NO | MUL | 0 | |
| activado | tinyint(1) unsigned | NO | | 1 | |
+--------------+---------------------+------+-----+------------+----------------+
34 rows in set (0.00 sec)

mysql> describe pagamento
-> ;
+----------------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+------------+----------------+
| id_pagamento | int(10) unsigned | NO | PRI | NULL | auto_increment |
| id_reg | int(11) | NO | MUL | 0 | |
| data_pagamento | date | NO | | 0000-00-00 | |
| montante | float | NO | | 0 | |
| obs | varchar(255) | NO | | | |
| num_guia | varchar(45) | YES | | NULL | |
+----------------+------------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)

And here is the query as it is now:

SELECT registo.id_reg, pagamento.data_pagamento
FROM registo
LEFT JOIN pagamento on registo.id_reg = pagamento.id_reg

As you can see, i'm selecting pagamento.data_pagamento witch is the date of payment and, if i've got 100 payments made to that record, this query will return me all those records.
What i want is to present all the records in table registo joined by the last record in table pagamento.

Thank you for your support,

Jorge Ferreira

Options: ReplyQuote


Subject
Views
Written By
Posted
Query
2587
March 15, 2006 11:08AM
1903
March 15, 2006 11:17AM
1874
March 15, 2006 11:37AM
1779
March 17, 2006 08:38AM


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.