php - mysql join three tables and grouping

Question

I have three tables:

products:

id   name
1    juice
2    chips
3    water

orders:

id   product_id   order_id
1    1            special1
2    3            special1
3    2            special1
4    1            special2
5    2            special2

final_orders:

id   order_id   date
1    special1   25-3-2017
2    special2   25-3-2017

I want to select all products names in every order using order_id to show:

ID: Special1
Date: 25-3-2017
Products List:
juice
water
chips

ID: Special2
Date: 25-3-2017
Products List:
juice
chips

I use this:

$sql = "select * from products,orders where products.id = orders.product_id";

but it doesn't work and show me duplicated results.

thank you.

Do you want to include `final_orders` in the query? What output do you want? — Tim Biegeleisen, Mar 25 '17 at 11:03

Tim Biegeleisen · Answer 1 · 2017-03-25T11:28:45.033

0

If you want to see one final order per record in your result set, then you will have to aggregate the products which appear in each order. One option then is the following query which aggregates order products into CSV using MySQL's GROUP_CONCAT():

SELECT t1.order_id,
       t1.date,
       t2.products
FROM final_orders t1
INNER JOIN
(
    SELECT a.order_id, GROUP_CONCAT(b.name) AS products
    FROM orders a
    INNER JOIN products b
        ON a.product_id = b.id
    GROUP BY a.order_id
) t2
    ON t1.order_id = t2.order_id

Demo here:

Rextester

edited Mar 25 '17 at 11:28

answered Mar 25 '17 at 11:10

Tim Biegeleisen

502,043
27
286
360

thank you! but how can I select more data from products and orders tables? I have quantity (in orders), price (in products) etc. – sadssdsada Mar 25 '17 at 12:59

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

You need to join with final_orders as well:

SELECT *
FROM final_orders AS f
JOIN orders AS o ON f.order_id = o.order_id
JOIN products AS p ON p.id = o.product_id
ORDER BY f.order_id

To prevent duplication in the output, your loop that prints the output should only show the information from final_orders when it changes. See How can i list has same id data with while loop in PHP?

edited May 23 '17 at 10:30

Community

1
1

answered Mar 25 '17 at 11:11

Barmar

741,623
53
500
612

Even after you followed the instructions in the question I linked to? – Barmar Mar 25 '17 at 11:28
yes I get the same results when I use: select * from products,orders where products.id = orders.product_id I think i should drop final_orders table and select all products name by joining products and orders and group results by order_id so I get for every code the name of products linked to it. – sadssdsada Mar 25 '17 at 11:29
Don't try to put code in comments. Add it to the question. – Barmar Mar 25 '17 at 11:30
I mean did you write the PHP loop that prints the results so that it only shows the order ID when it changes? – Barmar Mar 25 '17 at 11:30

php - mysql join three tables and grouping

2 Answers2

Rextester