MYSQL - Select 2 tables

Question

MYSQL - Select 2 tables

Navigation

#1 by (0 votes)

2

Good morning everyone!

I'm having a problem querying MYSQL, and the query I'm using is not displaying the results you want. Here's the example:

TABLE: CLIENTES (codigo_cliente, nome, cnpj) TABLE: HISTORICO_CONTATOS (protocolo, codigo_cliente, data, hora)

I need to display in a grid all the companies in the TABLE: CLIENTES and inform you of the date of the last contact registered in the TABLE:% with_of_these clients, eg:

TABLE: CUSTOMERS

CODIGO_CLIENTE---NOME---CNPJ

 - 1---Empresa1---48.989.048/0001-48
 - 2---Empresa2---58.789.048/0001-45
 - 3---Empresa3---38.889.048/0001-62 
 - 4---Empresa4---98.289.048/0001-02

**TABELA:HISTORICO_CONTATOS**

PROTOCOLO---CODIGO_CLIENTE---DATA---HORA

 - 1001---3---10/01/2016---09:10
 - 1002---1---13/01/2016---08:30
 - 1003---1---23/02/2016---11:15
 - 1004---2---23/02/2016---11:16
 - 1005---1---25/02/2016---08:16
 - 1006---3---29/02/2016---16:16
 - 1007---1---03/03/2016---07:30

**EXIBIÇÃO DESEJADA**

CODIGO_CLIENTE---NOME---DATA---HORA

 - 4---EMPRESA4---00/00/0000---00:00
 - 2---EMPRESA2---23/02/2016---11:16
 - 3---EMPRESA3---29/02/2016---16:16
 - 1---EMPRESA1---03/03/2016---07:30

As I said, I need a query that shows all the clients (without duplicity) and in the line of the client, informing the date of the last contact of this client, so for the others. If there is no contact, it has to display the client anyway, and the date would be blank.

Currently the query is as follows:

$sql = "SELECT c.cli_codigo, c.cli_nome, c.cli_bairro, c.cli_cidade, c.cli_vinculo, c.cli_ultimostatus, MAX(h.tel_datacontato) as tel_datacontato
            FROM cli_clientes c INNER JOIN tel_historico h ON c.cli_codigo = h.cli_codigo {$where}
            GROUP BY c.cli_codigo ORDER BY {$order_by} {$limit}";

Someone can help

php mysql join query inner

asked by anonymous 08.11.2016 / 14:08

1 answer

The entity type 'Personal' is part of a hierarchy, but does not have a discriminator value configured Create a multiple filter with only one input

score 0 · Answer 1

Instead of INNER JOIN use LEFT JOIN . The INNER requires you to register in both tables, LEFT imperatively takes into account the reference table.

Your query would look like this:

SELECT c.cli_codigo,
       c.cli_nome,
       c.cli_bairro,
       c.cli_cidade,
       c.cli_vinculo,
       c.cli_ultimostatus,
       MAX(h.tel_datacontato) as tel_datacontato
  FROM cli_clientes c
  LEFT JOIN tel_historico h ON c.cli_codigo = h.cli_codigo
 {$where}
 GROUP BY c.cli_codigo
 ORDER BY {$order_by}
 {$limit}

EDIT 1

With subquery to improve performance:

SELECT c.cli_codigo,
       c.cli_nome,
       c.cli_bairro,
       c.cli_cidade,
       c.cli_vinculo,
       c.cli_ultimostatus,
       (SELECT MAX(h.tel_datacontato)
          FROM tel_historico h
         WHERE c.cli_codigo = h.cli_codigo) as tel_datacontato
  FROM cli_clientes c
 {$where}
 ORDER BY {$order_by}
 {$limit}