SELECT on multiple SQLITE tables

Question

SELECT on multiple SQLITE tables

Navigation

#1 by (2 votes)

0

I have a customer table and I list all items in this table in SQLite I also need to list these clients, also get the lowest date, referring to the client, in another table.

Example:

**Tabela de Clientes**
id | codigo_cliente | nome_cliente
 1 |     0001       |     Joao
 2 |     0002       |     Maria
 3 |     0003       |     Carlos
 4 |     0004       |     Jose
 5 |     0005       |     Antonio
 6 |     0006       |     Oscar

**Tabela de Visitas**
id | codigo_cliente | data_visita
 1 |      0001      | 01/05/2017
 2 |      0001      | 03/06/2017
 3 |      0001      | 22/10/2017
 4 |      0002      | 15/05/2017
 5 |      0002      | 20/02/2017
 6 |      0004      | 04/03/2017
 7 |      0004      | 18/10/2017
 8 |      0004      | 13/05/2017
 9 |      0004      | 01/02/2017
10 |      0006      | 03/06/2017
11 |      0006      | 18/02/2017

In this case, I need to list the clients and together with the least visitation date of the Visitor Table:

**Lista de Clientes**
id | codigo_cliente  | nome_cliente | data_vista
1 |      0001       |     Joao     | 01/05/2017
2 |      0002       |     Maria    | 20/02/2017
3 |      0003       |     Carlos   |
4 |      0004       |     Jose     | 18/02/2017
5 |      0005       |     Antonio  |

Please, what would this SELECT look like in SQLite?

java android sqlite

asked by anonymous 09.03.2017 / 23:49

1 answer

regex java replace all How to securely store AWS credentials in a Java application?

score 2 · Answer 1

Just do a GROUP BY and MIN() :

SELECT c.id, c.codigo_cliente, c.nome_cliente, MIN(v.data_visita) AS data_visita
  FROM CLIENTES c
 INNER JOIN VISITAS v ON c.codigo_cliente = v.codigo_cliente
 GROUP BY c.id, c.codigo_cliente, c.nome_cliente
;

Note that GROUP BY must be all columns that will not receive an aggregate function (in this case, MIN() ). Other than that, it's a fairly simple SQL.