I often come across the issue to return one row from detail table like invoice items or order details for one customer for specific condition.
i.e. show me the max order product for each customer and order
that kind of query could be solved using self join, grouping and having clause
we have three tables, 1. customer 2. Order 3. OrderItem
the query look like
select c.customer_id, oi.order_id, oi.product_cost from order_item oi inner join order_item oii on oi.order_id = oii.order_id inner join customer c on c.customer_id = oi.customer_id group by c.customer_id, oi.order_id, oi.product_cost having oi.product_cost >= max(oii.product_cost)