SELF JOIN

-- Employee-manager hierarchy (self-referencing table)
SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find employees with the same manager
SELECT
  e1.name AS employee1,
  e2.name AS employee2,
  m.name  AS shared_manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id
JOIN employees m  ON e1.manager_id = m.id;

-- Category parent-child hierarchy
SELECT
  c.name AS category,
  p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

-- Same table, different aliases (required!)
-- Always alias both copies with different names

-- 3-level org chart: employee → manager → director
SELECT
  e.name        AS employee,
  m.name        AS manager,
  d.name        AS director,
  e.department,
  e.salary
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees d ON m.manager_id = d.id
ORDER BY d.name, m.name, e.name;

-- Find all direct reports of a specific manager
SELECT
  e.name,
  e.title,
  e.salary,
  e.department
FROM employees e
JOIN employees manager ON e.manager_id = manager.id
WHERE manager.name = 'Ananya Krishnan'
ORDER BY e.salary DESC;

-- Products frequently bought together (self-join on order_items)
SELECT
  p1.name AS product_1,
  p2.name AS product_2,
  COUNT(*) AS times_together
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id
                     AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.id
JOIN products p2 ON oi2.product_id = p2.id
GROUP BY p1.id, p1.name, p2.id, p2.name
HAVING COUNT(*) >= 5
ORDER BY times_together DESC;