Retrieve Data with Many-to-Many Relationships

SELECT
    p.product_name,
    GROUP_CONCAT(c.category_name SEPARATOR ', ') AS categories
FROM products p
JOIN product_categories pc ON p.product_id = pc.product_id
JOIN categories c ON pc.category_id = c.category_id
GROUP BY p.product_id, p.product_name
ORDER BY p.product_name;

How it works: This query demonstrates how to retrieve products and their associated categories from a many-to-many relationship. It joins the `products`, `product_categories` (junction table), and `categories` tables. `GROUP_CONCAT` (MySQL syntax) aggregates all category names for each product into a single comma-separated string, providing a concise result. For PostgreSQL or SQL Server, `STRING_AGG(c.category_name, ', ')` can be used instead. This is essential for displaying grouped related information in a single row.