SQL Join multiple tables only the 1st result

Question

SQL Join multiple tables only the 1st result

Navigation

#1 by (1 votes)

0

I have 3 tables:

**clientes**
cli_cliente   nome
50            A
52            B

**telefones_cliente**
cli_cliente  tel_telefone
50           387
50           386
50           385
52           400

**telefones**
tel_telefone    tel_contato
385             RODOLFO - COMPRAS
386             SONIA CONTAS A PAGAR RAMAL 201
387             VANESSA CONTABILIDADE RAMAL 202
388             (null)
389             (null)

I need customers and only the first phone in the customer_phone table to use this information to pick up the phones.tel_telephone and telephones.tel_contact. I've made several attempts to filter, but I always get all the client's phones back, I need help. This is my last SQL that does not work either.

SELECT
c.cli_cliente
,cli_razao_social,
tc.tel_telefone
--  ,telefones.tel_contato
  FROM
    clientes c
  JOIN telefones_cliente tc ON c.cli_cliente = (SELECT tc.cli_cliente FROM telefones_cliente, clientes c2, telefones t WHERE c2.cli_cliente=tc.cli_cliente AND tc.tel_telefone=t.tel_telefone LIMIT 1)

My result is this:

cli_cliente cli_razao_social                        tel_telefone
50          EXACTA ENGENHARIA E ADMINISTRAÇAO LTDA  385
50          EXACTA ENGENHARIA E ADMINISTRAÇAO LTDA  386
50          EXACTA ENGENHARIA E ADMINISTRAÇAO LTDA  387

When I need this:

cli_cliente cli_razao_social                        tel_telefone
49          WOOD FORM LTDA                          384
50          EXACTA ENGENHARIA E ADMINISTRAÇAO LTDA  387

sql join postgresql

asked by anonymous 12.07.2018 / 15:31

1 answer

How to set the time of a timestamp to 23:59:59 in postgresql? Unchanging collection by adding strings

score 1 · Answer 1

I suggest that you use some group function such as MAX or MIN to return only one phone.

Something like this:

SELECT 
   c.cli_cliente,
   c.nome,
   tc.tel_telefone 
FROM 
   clientes c 
   JOIN (
      SELECT 
         tc.cli_cliente,
         MAX(tc.tel_telefone) tel_telefone
      FROM 
         telefones_cliente tc
      GROUP BY 
         tc.cli_cliente
   ) tc ON tc.cli_cliente = c.cli_cliente