INNER JOIN repeating data between tablets

Asked

Viewed 43 times

-2

Good afternoon, I have a table of clients and plans that have keys between them, and I need to list all clients and show the name of the plans if the client has any linked plan. Clients q have no linked plan appear with the repeat plan of the previous line.

SELECT clientes.id, clientes.chave,  clientes.chave_plano, clientes.chave_sistema, planos.chave AS c_plano, planos.nome AS nome_plano FROM clientes  JOIN planos ON clientes.chave_plano = planos.chave OR clientes.chave_plano IS NULL;

inserir a descrição da imagem aqui

Can you help me ?

LEFT OUTER JOIN inserir a descrição da imagem aqui

insert image description here

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

mysql

  • If you want to list all clients and when you don’t have a plan associated the plan data is NULL then use LEFT OUTER JOIN and not INNER JOIN.

  • Thanks for your help, but LEFT OUTER JOIN also repeated the name of the plan, I put an image with LEFT in my question

  • Post the definition of the involved tables together with the indication of the primary and foreign keys. Take this condition OR clientes.chave_plano IS NULL.

  • I cloned the images in the question. if remove OR clients.chave_plano IS NULL does not list clients without linked plans

  • Here at Sopt posting images is not a suitable practice. See: Manual on how NOT to ask questions.

  • With INNER JOIN you will certainly not list, with LEFT OUTER JOIN you will certainly list.

  • Now it worked, thanks. Thanks for the manual, I’m new here :D

Show 2 more comments

1 answer

1


The JOIN is not suitable for what you want.

By establishing two conditions for the JOIN with the connective OR it will make the junction if one of the two is true, or both.

This returns repeated lines, because the JOIN makes a Cartesian product and selects only the lines that meet the condition (to see this test with a JOIN unconditional).

I recommend using a UNION:

SELECT * FROM
(
(SELECT clientes.id, clientes.chave,  clientes.chave_plano, clientes.chave_sistema, planos.chave AS c_plano, planos.nome AS nome_plano FROM clientes  JOIN planos ON clientes.chave_plano = planos.chave)

UNION

(SELECT clientes.id, clientes.chave,  clientes.chave_plano, clientes.chave_sistema, NULL AS c_plano, NULL AS nome_plano FROM clientes WHERE clientes.chave_plano IS NULL)
)

ORDER BY id;

(I didn’t test here to see if the syntax is correct, but that’s the idea)

Or you can also create a plan with null key (if you can).

Or even (which I think is more appropriate) would be to create a plan Não possui plano contratado, or similar. And treat this in your client application.

Browser other questions tagged mysql

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