Personal Catalogue: PHP & MySQL — Chapter 5, Exercise 3
====================================================

TASK
Run the tag-filter query from this chapter's own closing section
against your real database for a tag you've actually used, and write
out the full result. Then explain, in one or two sentences, why a book
tagged with two different tags only appears once in a plain
SELECT DISTINCT items.* FROM ... version of this query but could
appear twice without the DISTINCT.

SOLUTION

Running the chapter's own query for the tag "Fantasy" (using the data
from Exercises 1-2):

    SELECT items.*
    FROM items
    JOIN item_tags ON item_tags.item_id = items.id
    JOIN tags ON tags.id = item_tags.tag_id
    WHERE tags.name = 'Fantasy'
    ORDER BY items.title;

Expected output (values will differ based on your own real data):

    +----+--------------+-------------+-------------------+---------------+--------------+-------+---------------------+
    | id | item_type_id | title       | creator            | format_detail | release_year | notes | created_at          |
    +----+--------------+-------------+-------------------+---------------+--------------+-------+---------------------+
    |  4 |            1 | The Hobbit  | J.R.R. Tolkien     | NULL          |         NULL | NULL  | 2026-09-08 ...      |
    +----+--------------+-------------+-------------------+---------------+--------------+-------+---------------------+

Explanation of the DISTINCT question:

The query joins items to item_tags to tags — a JOIN produces one
output row for every matching combination of rows across the joined
tables. If a book is tagged with two different tags (say, both
"Fantasy" and "Adventure"), it has two real rows in item_tags — one
per tag. A query filtering for either of those tags individually
(WHERE tags.name = 'Fantasy') still only matches one of those two
item_tags rows, so the book appears once — no duplication in that
specific case.

But if the WHERE clause instead filtered for a set of several tags at
once (e.g. WHERE tags.name IN ('Fantasy', 'Adventure')) to find items
matching ANY of several tags, a book carrying both tags would match
both of its own item_tags rows, producing two output rows for the
same book — one per matching tag. SELECT DISTINCT items.* collapses
those duplicate item rows back down to one, since DISTINCT compares
whole rows and every column in items.* would be identical across both
matches.

WHY THIS WORKS AS AN ANSWER
----------------------------
It runs the real query against real data rather than a hypothetical
result, and correctly identifies that the single-tag WHERE clause in
the chapter's own example doesn't actually produce duplicates by
itself — the duplication risk only appears once the query is extended
to match several tags at once, which is exactly the scenario
DISTINCT exists to guard against.