Query Query MySQL

Question

Query Query MySQL

Navigation

#1 by (1 votes)

1

I have a small question and difficulty in trying to solve my problem, I have the following query:

SELECT
  'PcldCliente'.'id',
  'PcldCliente'.'nome',
  'PcldCliente'.'agencia_id',
  'PcldMain'.'id',
  'PcldMain'.'cliente_id',
  'PcldMain'.'saldo_devedor',
  'PcldMain'.'motivo',
  'PcldMain'.'data_inicio',
  'PcldDespesa'.'id',
  'PcldDespesa'.'cliente_id',
  'PcldDespesa'.'atual',
  'PcldDespesa'.'projetado',
  'PcldDespesa'.'data_inicio',
  'PcldObservacoes'.'id',
  'PcldObservacoes'.'cliente_id',
  'PcldObservacoes'.'observacao',
  'PcldObservacoes'.'created',
  'PcldSaldo'.'id',
  'PcldSaldo'.'cliente_id',
  'PcldSaldo'.'anterior',
  'PcldSaldo'.'atual',
  'PcldSaldo'.'projetado',
  'PcldSaldo'.'data_inicio'
FROM
  'mpf'.'pcld_cliente' AS 'PcldCliente'
LEFT JOIN
  'mpf'.'pcld_main' AS 'PcldMain' ON(
    'PcldMain'.'cliente_id' = 'PcldCliente'.'id'
  )
LEFT JOIN
  'mpf'.'pcld_despesa' AS 'PcldDespesa' ON(
    'PcldDespesa'.'cliente_id' = 'PcldCliente'.'id'
  )
LEFT JOIN
  'mpf'.'pcld_observacoes' AS 'PcldObservacoes' ON(
    'PcldObservacoes'.'cliente_id' = 'PcldCliente'.'id'
  )
LEFT JOIN
  'mpf'.'pcld_saldo' AS 'PcldSaldo' ON(
    'PcldSaldo'.'cliente_id' = 'PcldCliente'.'id'
  )
WHERE
  'PcldMain'.'data_inicio' = '2016-03-07' AND 'PcldDespesa'.'data_inicio' = '2016-03-07' AND 'PcldSaldo'.'data_inicio' = '2016-03-07' AND 'PcldObservacoes'.'created' = '2016-03-10'

What do I want? I want to return all Clients that have the records with the dates, but I want to return all the Observations for the specified date, regardless of whether or not it exists. Only it returns only the ones with the specified date, if it does not have the record in the PcldObservacoes table, it is not returned and stays outside. What can I do?

mysql query

asked by anonymous 14.03.2016 / 14:19

1 answer

Check if page comes from Google? Vraptor task does not work

score 1 · Accepted Answer

Solved!
As the user Marconcilio Souza spoke, just put the codintions on the ON instead of WHERE!

SELECT
  'PcldCliente'.'id',
  'PcldCliente'.'nome',
  'PcldCliente'.'agencia_id',
  'PcldMain'.'id',
  'PcldMain'.'cliente_id',
  'PcldMain'.'saldo_devedor',
  'PcldMain'.'motivo',
  'PcldMain'.'data_inicio',
  'PcldDespesa'.'id',
  'PcldDespesa'.'cliente_id',
  'PcldDespesa'.'atual',
  'PcldDespesa'.'projetado',
  'PcldDespesa'.'data_inicio',
  'PcldSaldo'.'id',
  'PcldSaldo'.'cliente_id',
  'PcldSaldo'.'anterior',
  'PcldSaldo'.'atual',
  'PcldSaldo'.'projetado',
  'PcldSaldo'.'data_inicio',
  'PcldObservacoes'.'id',
  'PcldObservacoes'.'cliente_id',
  'PcldObservacoes'.'observacao',
  'PcldObservacoes'.'created'
FROM
  'mpf'.'pcld_cliente' AS 'PcldCliente'
LEFT JOIN
  'mpf'.'pcld_main' AS 'PcldMain' ON(
    'PcldMain'.'cliente_id' = 'PcldCliente'.'id' AND 'PcldMain'.'data_inicio' = '2016-03-07'
  )
LEFT JOIN
  'mpf'.'pcld_despesa' AS 'PcldDespesa' ON(
    'PcldDespesa'.'cliente_id' = 'PcldCliente'.'id' AND 'PcldDespesa'.'data_inicio' = '2016-03-07'
  )
LEFT JOIN
  'mpf'.'pcld_saldo' AS 'PcldSaldo' ON(
    'PcldSaldo'.'cliente_id' = 'PcldCliente'.'id' AND 'PcldSaldo'.'data_inicio' = '2016-03-07'
  )
LEFT JOIN
  'mpf'.'pcld_observacoes' AS 'PcldObservacoes' ON(
    'PcldObservacoes'.'cliente_id' = 'PcldCliente'.'id' AND 'PcldObservacoes'.'created' = '2016-03-10'
  )
WHERE
  1 = 1
ORDER BY
  'PcldDespesa'.'projetado' DESC