MYSQL - Select 2 tables

Question

Good morning Personal!

I have a problem when performing a query in MYSQL, and the query I am using is not displaying the desired results. Follow the example:

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

I need to display in a grid all TABLE companies:CLIENTES and indicate the date of the last contact recorded in the TABLE:HISTORICO_CONTATOS of these customers, example:

SCHEDULE: 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 before, I need a query that shows all clients (without duplicity) and on the client’s line inform what was the date of the last contact of this client, so for the others. If there is no contact, it has to display the customer 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}";

Can anyone help

Answer 1

Instead of INNER JOIN utilize LEFT JOIN. The INNER requires registration in both tables, the LEFT takes imperative account of the reference table.

Your query would be as follows:

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}