SQL with empty Join

Asked

Viewed 108 times

1

I need to do this query with Mysql returning according to year.

But for those who have no workload the field should be NULL

Modelo SQL

For example: year 2017

SELECT
    `turmas_has_estudantes`.`id`,
    `turmas_has_estudantes`.`numero`,
        `estudantes_identificacao`.`nome_completo`,
        `estudantes_carga_horaria`.`dias_letivos_anuais`,
        `estudantes_carga_horaria`.`ch_diaria`,
        `estudantes_carga_horaria`.`ano_letivo`
    FROM
        `turmas_has_estudantes`
    LEFT JOIN `estudantes_identificacao` ON `estudantes_identificacao`.`id` = `turmas_has_estudantes`.`estudantes_identificacao_id`
    LEFT JOIN `estudantes_carga_horaria` ON `estudantes_carga_horaria`.`estudantes_identificacao_id` = `turmas_has_estudantes`.`estudantes_identificacao_id`
    WHERE
        `turmas_has_estudantes`.`turmas_id` = 535 AND         `estudantes_carga_horaria`.`ano_letivo` = 2017 OR         `estudantes_carga_horaria`.`ano_letivo` IS NULL
    ORDER BY
        `turmas_has_estudantes`.`numero` ASC,
`estudantes_identificacao`.`nome_completo` ASC
mysql
  • 1

    Take Where conditions on the table that you want to appear. Conditions in Where in practice turn left joins into Inner joins.

  • @bfvaretto If I take the Where appear other years I need only the years 2017 and if I don’t have the field stay NULL

  • 2

    Try moving this stretch of Where to the ON: AND estudantes_carga_horaria.ano_letivo = 2017 OR estudantes_carga_horaria.ano_letivo IS NULL

  • And then it worked?

  • @bfavaretto Sorry for the delay. I had to settle one thing here before It went super fine. Thanks!!! How do you put that the answer is right?

  • I’m going to put it down there as an answer, this dialogue of ours is as a comment and you can’t mark it

  • @bfvaretto Do you use Laravel? I want to do this query with query Builder

  • That would be another question. Post a separate question about how to mount the query with the Builder query. There are a lot of people here on the site who can help.

Show 3 more comments

1 answer

4


When you use LEFT JOIN but puts related table conditions in the WHERE, Join may end up behaving like INNER. Do so:

SELECT
    `turmas_has_estudantes`.`id`,
    `turmas_has_estudantes`.`numero`,
    `estudantes_identificacao`.`nome_completo`,
    `estudantes_carga_horaria`.`dias_letivos_anuais`,
    `estudantes_carga_horaria`.`ch_diaria`,
    `estudantes_carga_horaria`.`ano_letivo`
FROM `turmas_has_estudantes`
    LEFT JOIN `estudantes_identificacao` 
    ON `estudantes_identificacao`.`id` = `turmas_has_estudantes`.`estudantes_identificacao_id`

    LEFT JOIN `estudantes_carga_horaria` 
    ON `estudantes_carga_horaria`.`estudantes_identificacao_id` = `turmas_has_estudantes`.`estudantes_identificacao_id`
        AND (`estudantes_carga_horaria`.`ano_letivo` = 2017 OR `estudantes_carga_horaria`.`ano_letivo` IS NULL)   
WHERE turmas_has_estudantes`.`turmas_id` = 535 
ORDER BY
    `turmas_has_estudantes`.`numero` ASC,
    `estudantes_identificacao`.`nome_completo` ASC

Notice that it was necessary to place parentheses in its original expression, because it uses OR.

Browser other questions tagged mysql

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