Personal Catalogue: PHP & MySQL — Chapter 7, Exercise 2
====================================================

TASK
Temporarily change the list query's own LEFT JOIN item_tags to a
plain JOIN item_tags, reload index.php, and describe exactly what
changes in the result set. Then change it back and confirm the
original behavior returns.

SOLUTION

Changing:

    LEFT JOIN item_tags ON item_tags.item_id = items.id
    LEFT JOIN tags ON tags.id = item_tags.tag_id

to:

    JOIN item_tags ON item_tags.item_id = items.id
    JOIN tags ON tags.id = item_tags.tag_id

and reloading index.php: every item with at least one tag still
appears correctly (e.g. "The Hobbit" still shows "Adventure,
Fantasy"), but every item with zero tags disappears from the list
entirely — "Fluent Python" and "The Dark Side of the Moon" from
Exercise 1 are both no longer shown at all, not shown with an empty
Tags column.

This happens because a plain (inner) JOIN only produces an output row
when a match exists on both sides of the join. An item with no rows
in item_tags has nothing to match against on the right-hand side of
JOIN item_tags, so that item's own row is dropped from the result set
before it even reaches the GROUP BY step — it isn't that tag_names
comes back empty for it, it's that the item itself never appears in
$items at all.

Confirm directly at the mysql> prompt:

    SELECT COUNT(*) FROM items;
    -- e.g. returns 3 (all real items)

    SELECT COUNT(*) FROM (
        SELECT items.id FROM items
        JOIN item_tags ON item_tags.item_id = items.id
        GROUP BY items.id
    ) AS matched;
    -- returns only the count of items that have at least one tag, e.g. 1

Reverting both JOINs back to LEFT JOIN restores all three items to the
list, with untagged ones correctly showing the "—" fallback from
Exercise 1 again.

WHY THIS WORKS AS AN ANSWER
----------------------------
It describes the real, correct symptom (untagged items vanish
entirely, rather than showing blank) and explains the actual
mechanism (an inner join requires a match on both sides, so no
matching item_tags row means no output row for that item at all),
confirmed with a direct count comparison at the database level.