SELECT in multiple SQLITE tables

Asked

Viewed 147 times

0

I have a table of clients and I make a list of all the items in this table in SQLite I need to list these customers, also search for the lowest date, related to the customer, 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 customers and that together comes the lowest date of visit of the Table of Visits:

**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, how would this SELECT in Sqlite?

java android sqlite

1 answer

2

Just make 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 the GROUP BY has to be for all columns that will not receive an aggregator function (in this case, MIN()). Other than that, it’s a pretty simple SQL.

  • Thank you very much Wtrmute, it worked. But I forgot to mention that eventually it may happen that some customers have no record in the VISITS table. Thus, I need to list this client even if there is no record in the VISITS table.

  • only change to 'INNER JOIN VISITAS v ON c.codigo_client = v.codigo_client OR c.codigo_client IS NOT NULL', I don’t have time to test at the moment, but it’s worth a try

  • Thanks Mathias, I tested here and netse if it returns the same date to all customers, until customers who have no date.

  • I need in this case, if there is no date related to this client in the VISITS table, that the client appears on the list with the same empty date.

  • In that case, it’s like I wrote, but LEFT JOIN or LEFT OUTER JOIN in place of INNER JOIN.

  • It worked this last option Wtrmute, using LEFT JOIN. Thank you so much for your help.

Show 1 more comment

Browser other questions tagged java android sqlite

You are not signed in. Login or sign up in order to post.