Unexpected Result in SELECT

Asked

Viewed 31 times

0

I have a SELECT that does Join in three tables, taking order data, requested client, responsible assistant and values related to orders.

all the Clientes and Assistentes duplicates in that query are returning as NULL, in case they wanted their names to show

QUERY:

SELECT pedido.id,pedido.local,pedido.data_hora,pedido.status,cliente.nome as nomeCliente,assistente.nome as nomeAssistente,pagamento.valor_bruto,pagamento.valor_liquido,pagamento.imposto
FROM pedido LEFT JOIN pagamento
ON pedido.id = pagamento.pedido_id
LEFT OUTER JOIN assistente
ON pedido.id = assistente.id
LEFT OUTER JOIN cliente
ON pedido.id = cliente.id
ORDER BY pedido.id;

Output:

+----+----------------------------------------------------------------------------+---------------------+--------+--------------------------+------------------------+-------------+---------------+---------+
| id | local                                                                      | data_hora           | status | nomeCliente              | nomeAssistente         | valor_bruto | valor_liquido | imposto |
+----+----------------------------------------------------------------------------+---------------------+--------+--------------------------+------------------------+-------------+---------------+---------+
|  1 | Avenida do Contorno                                                        | 2016-06-25 19:38:00 |      1 | Janaina de Araujo Amaral | Alberto Fonseca Araujo |       39.65 |         29.65 |      10 |
|  2 | Rua Portugal                                                               | 2016-05-30 14:54:00 |      1 | Felipe Almeida           | Juliana Campos         |        25.4 |          15.4 |      10 |
|  3 | Rua Dido                                                                   | 2016-05-25 10:34:00 |      1 | Mariana Ferreira         | Mauro Fernandes Pinto  |          12 |             2 |      10 |
|  4 | Avenida Engenheiros                                                        | 2016-03-12 08:16:00 |      1 | Marcello Lessa           | Fátima Veras de Souza  |       54.28 |         44.28 |      10 |
|  5 | R. Santa Cruz, 546 - Grajaú, Belo Horizonte - MG, 30431-228, Brasil       | 2016-09-18 00:54:00 |      1 | Jádonos Fieslorbe       | NULL                   |        NULL |          NULL |    NULL |
|  6 | R. Santa Cruz, 546 - Grajaú, Belo Horizonte - MG, 30431-228, Brasil       | 2016-09-18 00:55:57 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
|  7 | R. Marechal Hermes, 494-496 - Gutierrez, Belo Horizonte - MG, Brasil       | 2016-09-18 00:56:22 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
|  8 | R. Fagundes Varela, 135 - Lagoinha, Belo Horizonte - MG, 31210-310, Brasil | 2016-09-18 02:52:32 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
|  9 | R. Santa Cruz, 546 - Grajaú, Belo Horizonte - MG, 30431-228, Brasil       | 2016-09-18 08:28:06 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
| 10 |                                                                            | 2016-09-18 08:55:54 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
| 11 |                                                                            | 2016-09-18 08:56:00 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
| 12 | Rua Um, 54 - Conj. Confisco, Belo Horizonte - MG, 31360-480, Brasil        | 2016-09-18 08:58:41 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
| 13 | R. Rio Negro, 1357-1565 - Grajaú, Belo Horizonte - MG, 30431-115, Brasil  | 2016-09-18 10:10:40 |      1 | NULL                     | NULL                   |        NULL |          NULL |    NULL |
+----+----------------------------------------------------------------------------+---------------------+--------+--------------------------+------------------------+-------------+---------------+---------+
mysql database select
  • 1

    ON pedido.id = assistente.id and ON pedido.id = cliente.id are correct? wouldn’t it be ON pedido.assistente_id = assistente.id and ON pedido.cliente_id = cliente.id ?

  • @Édipocostarebouças Putz. That’s right, I was half an hour trying to find a silly mistake like this, Vlw

1 answer

1


The column of the requested table used in Join with the client table should be the client id, for example request.cliente_id, and not the order id. The same happens in Join with the wizard table

Browser other questions tagged mysql database select

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