Return distinct and null records from a mysql join query
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.
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
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.
精彩评论