Why my right join isn't working?

Question

I need to show all categories, even categories with no items.

I have this query.

SELECT
        i.id,
        incident_active 'Approved',
        incident_verified 'Verified',
        category_title 'Category',
        ParentCategory 'Parent Category'
    FROM
        incident i
            INNER JOIN
        incident_category ic ON i.id = ic.incident_id
            RIGHT JOIN
        incident_person ip ON i.id = ip.incident_id
            RIGHT JOIN
        (SELECT
            c1.id,
                c1.parent_id,
                c2.category_title ParentCategory,
                CONCAT_WS(' -> ', c2.category_title, c1.category_title) category_title
        FROM
            category c1
        left outer join category c2 ON c1.parent_id = c2.id WHERE c1.parent_id != 0) AS c ON c.id = ic.category_id
    WHERE incident_dateadd > DATE_SUB(NOW(), INTERVAL 1 MONTH)

which return:

enter image description here

and this query:

SELECT
            c1.id,
                c1.parent_id,
                c2.category_title ParentCategory,
                CONCAT_WS(' -> ', c2.category_title, c1.category_title) category_title
        FROM
            category c1
        left outer join category c2 ON c1.parent_id = c2.id WHERE c1.parent_id != 0

which return:

enter image description here

I've read several times this answer but I can not see why my right join isn't working.

The first result set should have 8 more columns, the columns of categories which parent is Protesta

UPDATE

I got it working whith the following query:

SELECT * FROM (SELECT
        i.id,
        incident_title 'Título',
        incident_description 'Descripción',
        incident_date 'Fecha',
        incident_active 'Aprobado',
        incident_verified 'Veficado',
        person_first 'Nombres',
        person_last 'Apellidos',
        person_email 'Email',
        category_id
        -- category_title 'Categoría',
        -- ParentCategory 'Categoría Padre'
    FROM
        incident i
            INNER JOIN
        incident_category ic ON i.id = ic.incident_id
            RIGHT JOIN
        incident_person ip ON i.id = ip.incident_id

    WHERE (incident_dateadd > DATE_SUB(NOW(), INTERVAL 1 MONTH) OR incident_dateadd IS NULL)) a
RIGHT JOIN

(SELECT
            c1.id,
                c1.parent_id,
                c2.category_title ParentCategory,
                CONCAT_WS(' -> ', c2.category_title, c1.category_title) category_title
        FROM
            category c1
        left outer join category c2 ON c1.parent_id = c2.id WHERE c1.parent_id != 0) b ON a.category_id = b.id

Although I still don't understand why it was not working with the first version, in my mind both queries are equivalent.

If anyone could explain the differences...

Even if you are performing a `RIGHT JOIN`, you are still using a filter on a column from the other tables (`WHERE incident_dateadd > DATE_SUB(NOW(), INTERVAL 1 MONTH)`) so it effectively filters out every row that doesn't exists on the `incident` table — Lamak, Oct 08 '14 at 20:25
@Lamak I made a version that 'works' but I still don't see clearly why mi first version does not work. I appreciate if you have any comments. — Cesar, Oct 08 '14 at 20:44
I had a comment, but it seems I didn't explain myself clearly. So, to simplify, let's assume you only have the `incident` and `category` tables. If you do `SELECT * FROM incident i RIGHT JOIN category ON ` and then perform a filter **on a column from the incident table** `WHERE i.col1 > something`, then, by definition, when a category that didn't have an incident (hence `i.column` is `NULL`) it will be filter out of your result, effectively undoing the reason for performing a `RIGHT JOIN` in the first place. (Also, I hate `RIGHT JOIN`s and always use `LEFT JOIN`s instead) — Lamak, Oct 08 '14 at 20:50

score 2 · Accepted Answer · answered Oct 08 '14 at 21:01

It's the location of your final where clause.

In your fist query, you pull all of your categories and associate them with a bunch of data, getting a compilation of rows. You then use a where clause to filter out many of those rows, some of which happen to be category rows.

Let's look at a simple example.

Table A:

X | Y
-----
1 | hi
2 | bye
3 | what

Table B:

Z | X
-----
A | 1
B | 1
C | 2

Given these tables, if I say the following

SELECT * FROM `B` RIGHT JOIN `A` ON A.X = B.X

my result will be:

Z | X | Y
---------
A | 1 | hi
B | 1 | hi
C | 2 | bye
- | 3 | what

If, however, I add a where clause on the end of that so my query becomes

SELECT * FROM `B` RIGHT JOIN `A` ON A.X = B.X WHERE B.Z > 'A'

some of table A is filtered out. Now I have:

Z | X | Y
---------
B | 1 | hi
C | 2 | bye

However, if my query does the filtering before the join, like so:

SELECT * FROM
    (SELECT * FROM `B` WHERE B.Z > 'A') AS B
RIGHT JOIN `A` ON A.X = B.X

my table still contains all the rows from A.

Z | X | Y
---------
B | 1 | hi
C | 2 | bye
- | 3 | what

It's just a matter of order. In your original query, you select all the rows then filter out some. In your working query, you first filter, then you get all the category rows you need.

Why my right join isn't working?

UPDATE

1 Answers1