Unexpected Result in SELECT

Navigation
0

I have a SELECT that joins three tables, taking the data from the request, requesting client, responsible assistant, and values related to the requests.

all Clientes and Assistentes duplicates in this query are returning as NULL , in case you 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 |
+----+----------------------------------------------------------------------------+---------------------+--------+--------------------------+------------------------+-------------+---------------+---------+
    
asked by anonymous 18.09.2016 / 15:50

1 answer

1

The column of the request table used in join with the client table should be the client id, for example request.client_id, not the request id. The same thing happens in join with the wizard table

    
18.09.2016 / 16:15
Check if a specific item is selected in one of the combos on the page Import HTML table based on a URL and fill in a datatable