SQL JOINs — Working With Related Data

-- 1. DATA STORED SEPARATELY (Normalized in Disk Storage):
-- Table: users (id, name)
-- Table: orders (id, user_id [FK], total)

-- 2. DATA RETRIEVED TOGETHER (Joined on the Fly when Application Needs It):
SELECT 
    users.name, 
    orders.total
FROM users
INNER JOIN orders 
    ON users.id = orders.user_id;

-- What happens inside the database engine:
-- The ON clause acts as the matching predicate. For each row in users, the engine finds
-- all rows in orders where orders.user_id equals users.id, fusing them into unified output rows.

-- ✅ PostgreSQL (Native Support):
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- ⚠️ MySQL 8.4 (Does NOT support FULL OUTER JOIN natively):
-- You must emulate it using a UNION of LEFT JOIN and RIGHT JOIN:
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Instead of repeating long table names:
SELECT u.name, o.total
FROM users AS u
INNER JOIN orders AS o 
    ON u.id = o.user_id;

-- Fetch high-value completed orders:
SELECT 
    u.name, 
    o.id AS order_id, 
    o.total
FROM users u
INNER JOIN orders o 
    ON u.id = o.user_id
WHERE o.total >= 100.00
ORDER BY o.total DESC;

-- Full line-item detail report:
SELECT 
    u.name AS customer_name,
    o.id AS order_id,
    p.title AS product_name,
    oi.quantity,
    oi.unit_price
FROM users u
INNER JOIN orders o 
    ON u.id = o.user_id
INNER JOIN order_items oi 
    ON o.id = oi.order_id
INNER JOIN products p 
    ON oi.product_id = p.id;

// Route: GET /api/users/:id/courses
app.get('/api/users/:id/courses', async (req, res) => {
  const userId = req.params.id;

  const query = `
    SELECT 
      u.name AS student_name,
      c.title AS course_title,
      e.enrolled_at,
      e.progress_percentage
    FROM users u
    INNER JOIN enrollments e 
      ON u.id = e.user_id
    INNER JOIN courses c 
      ON e.course_id = c.id
    WHERE u.id = $1
    ORDER BY e.enrolled_at DESC;
  `;

  const result = await db.query(query, [userId]);

  // Serialized JSON Response sent to React Frontend:
  res.json({
    success: true,
    user_id: userId,
    student_name: result.rows[0]?.student_name || null,
    enrolled_courses: result.rows.map(r => ({
      title: r.course_title,
      enrolled_at: r.enrolled_at,
      progress: r.progress_percentage
    }))
  });
});