CODEWITHSUNDEEP
jquerymysqllistbox
user2146226
user2146226

Reputation: 43

SELECT from multiple tables and display in a table

I have two tables called order and order_items

order_id--pupil_id --date_bought--total_price

1  ----1001---- 2013-03-07 23:35:49 - 1.00

and

order_id-- product_name

1 ------ product1

1 ------ product2

I want to display date bought and total price. It will be some sort of join statement with the products that have been bought with the most recent order_id which would be order by desc maybe in the statement

I'm unsure on how to piece it together. Any ideas or clues on how to achieve this?

Upvotes: 2

Views: 129

Answers (2)

Shaun Campbell
Shaun Campbell

Reputation: 51

SELECT 
    od.date_bought, 
    od.total price, 
    group_concat(odi.product_name separator ', ') 
FROM order, order_items 
WHERE 
    order.order_id od = order_items.order_id odi 
GROUP BY odi.order_id
ORDER BY od.order_id DESC;

Upvotes: 0

Steve Chambers
Steve Chambers

Reputation: 39424

SELECT product_name, date_bought, total_price
FROM `order` o
JOIN `order_items` oi
ON o.order_id = oi.order_id
ORDER BY o.order_id DESC

Upvotes: 1

Related Questions