Using multiple inner joins

Question

I have four tables: users, orders, orders_product and products. They are connected to each other by foreign key

user tables contains: id, name, email and username.

product table contains: id, product_name, product_description and product_price

orders table contains: id, u_id(foreign key).

orders_product table contains: id, product_id(foreign key), order_id(foreign key).

Now I was trying to fetch the name of a user with the total price of a particular order that he has placed.

The maximum I could went for was something like this:

SELECT prod.order_id,
       SUM(product_price) AS Total
FROM products
INNER JOIN
  (SELECT orders.id AS order_id,
          orders_product.product_id
   FROM orders
   INNER JOIN orders_product ON orders.id = orders_product.order_id
   WHERE order_id=1) AS prod ON products.id = prod.product_id;

It showed me total price of a particular order. Now I have two questions:

Is that query correct. It looks like a very long query. Can the same result be achieved with a smaller one?
How to fetch the name of a user with the total price of a particular order that he has placed.

WHY do you need seperate table called orders? You can remove that. — jack
– jack, Commented Apr 28, 2014 at 11:12

GirginSoft · Accepted Answer · 2014-04-28 11:18:30Z

1

Hi some addition to @Gordon Linoff your query seems ok. if you store your price data in order_products it will be good and some benefit, one of these benefit is aggregation will be simple. Second benefit if product price change it will not affect to order.

answered Apr 28, 2014 at 11:18

GirginSoft

3763 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-04-28 13:34:57Z

1

Your query is correct for one order, but it can be improved:

Don't use a subquery unless necessary. In MySQL this introduces additional overhead.
You are only looking at one order, which seems on the light site. You should remove the where clause.
You should be using a group by because you want aggregation.
You need to join in the user table to get the name.

I also added table aliases (abbreviations for table names). This makes the query a bit more readable:

SELECT u.name, SUM(p.product_price) as Total
FROM orders_product op INNER JOIN
     orders o
     ON o.id = op.order_id  INNER JOIN
     products p
     ON p.id = op.product_id INNER JOIN
     users u
     on o.userid = u.id
WHERE op.order_id = 1
GROUP BY u.name;

edited Apr 28, 2014 at 13:34

answered Apr 28, 2014 at 11:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

Kanav Over a year ago

Can you add some brackets?..There are lots of INNER JOINs here :D. It would be a great help. Thanks.

Gordon Linoff Over a year ago

@user170654 . . . I could, but I won't. I find that parentheses in the from clause make the intent of the query much harder to follow. You should learn how to read queries. You start with the first pair of tables and continue from there.

Kanav Over a year ago

Then can you explain the query as I'm finding it hard to follow.

Gordon Linoff Over a year ago

@user170654 . . . I hope the edits that fix the query help. The from clause was message up. My apologies for that.

Jagadeesh Gudla · Accepted Answer · 2014-04-28 11:25:24Z

0

Your SQL is wrong. Because You want to calculate specific to user. But your SQL is specific to Order. Your SQL will give result for One Order. Please make it User Specific by giving user name or what ever is unique.

answered Apr 28, 2014 at 11:25

Jagadeesh Gudla

621 silver badge6 bronze badges

Collectives™ on Stack Overflow

Using multiple inner joins

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related