Return distinct and null records from a mysql join query

Question

Is there any way to return distinct values with blank/null data from a table join. Best to explain with my example below.

Table "orders"

order_id | order_total
1        | 10
2        | 20
3        | 50

Table "order_items"

item_id | order_id | name     | qty_ordered | base_price | row_total
1       | 1        | Product  | 1           | 10         | 10
2       | 2        | Product  | 1           | 10         | 10
3       | 2        | Product2 | 1           | 10         | 10
4       | 3        | Product  | 2           | 10         | 20
5       | 3        | Product2 | 3           | 10         | 30

I'm trying to produce a result set that looks like this.

order_id | item_id | name     | qty_ordered | base_price | row_total | order_total
1        | 1       | Product  | 1           | 10         | 10        | 10
2        | 2       | Product  | 1           | 10         | 10        | 20
null     | 3       | Product2 | 1           | 10         | 10        | null
3        | 4       | Product  | 2           | 10         | 20        | 50
null     | 5       | Product2 | 3           | 10         | 30        | null

I only want the order_id and order_total once per order. I figure this is possible with some sort of join/distinct/sub query but alas nothing I've tried has worked so far.

Answer 1

SELECT * FROM order_items
LEFT JOIN orders
ON (
     order_items.order_id=orders.order_id
AND
     order_items.item_id=(
         SELECT MIN(item_id)
         FROM order_items a
         WHERE a.order_id=order_items.order_id
     )
)

This should work because the nested query always returns the same MIN(item_id) for each order, and it only joins for that item.

But this is a very, very ugly piece of sql. Don't do this.

Answer 2

Use:

SELECT x.order_id,
       x.item_id,
       x.name,
       x.qty_ordered,
       x.base_price,
       x.row_total,
       x.order_total
  FROM (SELECT CASE 
                  WHEN @order = o.order_id THEN NULL 
                  ELSE o.order_id 
               END AS order_id,
               oi.item_id,
               oi.name,
               oi.qty_ordered,
               oi.base_price,
               oi.row_total,
               o.order_total,
               CASE 
                  WHEN @order = o.order_id THEN NULL 
                  ELSE o.order_total 
               END AS order_total,
               @order := o.order_id
          FROM ORDER_ITEMS oi 
          JOIN ORDERS o ON o.order_id = oi.order_id
          JOIN (SELECT @order := -1) r
      ORDER BY o.order_id, oi.item_id) x