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:

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:

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...

Answer 1

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.