SELECT to return rows even if one of the tables is empty

Question

I'm trying to SELECT some columns from a table (joined with other tables). It must return all the rows where 'beactive_actividades.id' is 2 or where 'beactive_res_colaboradores.interv_id' is 2 (on the rows that 'act.id'='colab.actividade_id').

My select is working, but only if there are any rows in beactive_res_colaboradores. If beactive_res_colaboradores is empty, it doesn't even return the rows where usr.id=2, which is opposed to what I need.

I need all the rows WHERE there is either one condition OR the other.

SELECT DISTINCT act.id, act.descricao, act.detalhes, act.urgencia
, est.id AS estado_id, est.descricao AS estado_desc
, tipo.id AS tipo_id, tipo.descricao AS tipo_desc
, cli.id AS cliente_id, cli.nome AS cliente_nome
, usr.id AS user_id, usr.nome AS user_nome
FROM beactive_actividades AS act
LEFT JOIN beactive_act_estados AS est ON act.estado_id=est.id
LEFT JOIN beactive_act_tipos AS tipo ON act.tipo_id=tipo.id
LEFT JOIN beactive_clientes AS cli ON act.cliente_id=cli.id
LEFT JOIN beactive_users AS usr ON act.interv_id=usr.id
, beactive_res_colaboradores AS colab
WHERE (usr.id=2 OR (colab.interv_id=2 AND act.id=colab.actividade_id))
AND est.id<6
ORDER BY act.urgencia DESC, est.id DESC

I forgot to say that beactive_res_colaboradores is an helper table that links one user_id to an actividade_id, so only the pair user_id+actividade_id is unique.

Why aren't you joining actividates and colaboradores? – Shomz Dec 02 '13 at 20:06 — Shomz, Dec 02 '13 at 20:06

Filipe Silva · Accepted Answer · 2013-12-03T10:06:54.917

2

You are doing an INNER JOIN with beactive_res_colaboradores ( the old fashion way with , and validation in WHERE) so you are removing every row that doesn't have a match in that table. Do LEFT JOIN instead:

SELECT DISTINCT act.id, act.descricao, act.detalhes, act.urgencia
, est.id AS estado_id, est.descricao AS estado_desc
, tipo.id AS tipo_id, tipo.descricao AS tipo_desc
, cli.id AS cliente_id, cli.nome AS cliente_nome
, usr.id AS user_id, usr.nome AS user_nome
FROM beactive_actividades AS act
LEFT JOIN beactive_act_estados AS est ON act.estado_id=est.id
LEFT JOIN beactive_act_tipos AS tipo ON act.tipo_id=tipo.id
LEFT JOIN beactive_clientes AS cli ON act.cliente_id=cli.id
LEFT JOIN beactive_users AS usr ON act.interv_id=usr.id
LEFT JOIN beactive_res_colaboradores AS colab ON act.id=colab.actividade_id
WHERE (usr.id=2 OR colab.interv_id=2) AND est.id<6
ORDER BY act.urgencia DESC, est.id DESC

edited Dec 03 '13 at 10:06

answered Dec 02 '13 at 20:08

Filipe Silva

21,189
5
53
68

This seems to be working for me. Just edit your answer to fix the syntax: remove '(' after 'OR' – Pedro Moreira Dec 03 '13 at 10:05
@PedroMoreira. To learn about the different types of JOINs, you should check [this question](http://stackoverflow.com/q/38549/1385896). – Filipe Silva Dec 03 '13 at 10:12
I meant MySQL in gerenal, not only JOINs. But thanks for that link, it has lots of good explanations! – Pedro Moreira Dec 04 '13 at 22:32
@PedroMoreira. For SQL in general, i heard great things about this course: https://www.coursera.org/course/db – Filipe Silva Dec 04 '13 at 23:12

score 0 · Answer 2 · answered Dec 02 '13 at 20:08

0

Use LEFT OUTER JOIN this will do the join as normal, but if a match isn't found for the left table then a row will be returned with nulls for the right table's columns

answered Dec 02 '13 at 20:08

David Garrison

2,546
15
25

SELECT to return rows even if one of the tables is empty

2 Answers2