Doubt Query Mysql

Asked

Viewed 36 times

1

I have a little doubt 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 I want?
I want to return all Customers who have the records with the start dates, but I want you to return all Notes to the specified date, regardless of whether they exist or not. Only it returns only those that have the date specified, if it does not have the record in the table PcldObservacoes he is not returned and stands outside.
What can I do?

mysql query
  • 1

    Remove your Where put it on the ON of your tables ... ON(
 Pcldmain.cliente_id=Pcldcliente.idAND Pcldmain.start date= '2016-03-07' ANDPclddespesa.start date = '2016-03-07' )

1 answer

1


Solved!
As the user Marconcilio Souza said, simply put the codings 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

Browser other questions tagged mysql query

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