Archives February 2015

SQL

Selectarea celor mai recente/vechi inregistrari in SQL JOIN

Lorand Szekely 0 Comment

Se considera tabela clienti, cu urmatoarea structura:

+-----------+-------------+
| client_id | client_name |
+-----------+-------------+
|         1 | Lorand      |
|         2 | Mihai       |
+-----------+-------------+

Si tabela adrese_clienti, cu urmatoarea structura:

+------------+-----------+----------------+---------------------+---------------------+
| address_id | client_id | client_address | date_from           | date_to             |
+------------+-----------+----------------+---------------------+---------------------+
|          1 |         1 | Adresa 1       | 2015-01-01 10:23:21 | 2015-01-17 11:24:31 |
|          2 |         1 | Adresa 2       | 2015-01-17 11:24:31 | 2015-02-21 13:28:57 |
|          3 |         1 | Adresa 3       | 2015-02-21 13:28:57 | 9999-01-01 00:00:00 |
|          4 |         2 | Adresa 4       | 2015-01-01 13:12:53 | 2015-01-31 09:19:22 |
|          5 |         2 | Adresa 5       | 2015-01-31 09:19:22 | 2015-02-11 08:23:19 |
|          6 |         2 | Adresa 6       | 2015-02-11 08:23:19 | 9999-01-01 00:00:00 |
+------------+-----------+----------------+---------------------+---------------------+

Pentru fiecare client se pastreaza istoricul modificarilor adreselor, in aceeasi tabela, fiecare inregistrare pastrand perioada de valabilitate in campurile date_from si date_to. Inregistrarea actuala pentru fiecare client este marcata de valoarea 9999-01-01 00:00:00 in campul date_to.

Doua probleme

1. Se doreste selectarea celei mai vechi adrese pentru fiecare client:

SELECT c.client_id, c.client_name, c2.client_address
FROM clienti AS c
INNER JOIN (
    SELECT client_id, MIN(address_id) AS address_id
    FROM adrese_clienti GROUP BY client_id
) AS c1 ON (c1.client_id = c.client_id)
INNER JOIN adrese_clienti AS c2 ON (c2.address_id = c1.address_id);

Rezultat:

+-----------+-------------+----------------+
| client_id | client_name | client_address |
+-----------+-------------+----------------+
|         1 | Lorand      | Adresa 1       |
|         2 | Mihai       | Adresa 4       |
+-----------+-------------+----------------+

2. Se doreste selectarea adresei actuale pentru fiecare client:

SELECT c.client_id, c.client_name, c2.client_address
FROM clienti AS c
INNER JOIN (
    SELECT client_id, MAX(address_id) AS address_id
    FROM adrese_clienti GROUP BY client_id
) AS c1 ON (c1.client_id = c.client_id)
INNER JOIN adrese_clienti AS c2 ON (c2.address_id = c1.address_id);

sau mai simplu (datorita campului date_to):

SELECT c.client_id, c.client_name, c1.client_address
FROM clienti AS c
INNER JOIN adrese_clienti AS ac
    ON (ac.client_id = c.client_id AND ac.date_to = '9999-01-01 00:00:00');

Rezultat:

+-----------+-------------+----------------+
| client_id | client_name | client_address |
+-----------+-------------+----------------+
|         1 | Lorand      | Adresa 3       |
|         2 | Mihai       | Adresa 6       |
+-----------+-------------+----------------+