Basic Many-to-Many - Students and Courses

basic

Show which students are enrolled in which courses using junction table

-- Junction table: enrollments (student_id, course_id, enrollment_date)

-- Query students with their enrolled courses

SELECT

s.student_name,

c.course_name,

c.credits,

e.enrollment_date

FROM students s

INNER JOIN enrollments e ON s.student_id = e.student_id

INNER JOIN courses c ON e.course_id = c.course_id

WHERE s.status = 'active'

ORDER BY s.student_name, c.course_name;

Shows active students with their enrolled courses and enrollment dates

Two JOINs through junction table: students→enrollments→courses. Junction table stores the many-to-many relationship.

All

Junction Table with Metadata - Product Categories

intermediate

Products can belong to multiple categories with priority and status metadata

-- Junction table: product_categories (product_id, category_id, priority, status, created_date)

-- Find products in multiple categories with their priority

SELECT

p.product_name,

p.price,

c.category_name,

pc.priority,

pc.status,

pc.created_date

FROM products p

INNER JOIN product_categories pc ON p.product_id = pc.product_id

INNER JOIN categories c ON pc.category_id = c.category_id

WHERE pc.status = 'active'

AND pc.priority <= 3 -- High priority categories only

ORDER BY p.product_name, pc.priority;

Shows products with their active high-priority categories and metadata

Junction table contains metadata (priority, status, created_date) beyond just the relationship keys.

All

Complex Many-to-Many Analysis - User Roles and Permissions