Multiple Subselects to same table in mysql

Question

I have a product-table and a table containing all parts of a product and the quantity.

Structure of products table:

id 
name

Structure of parts table:

id
productsId
partsId
quantity

Now I want to get all products and for every product the total quantity parts and quantity of different parts . The current solution is this query:

SELECT
    products.*,
    (
        SELECT count(quantity) 
        FROM product_has_part 
        WHERE product_has_part.productsId = products.id
    ) AS partsQty,
    (
        SELECT sum(quantity) 
        FROM product_has_part 
        WHERE product_has_part.productsId = products.id
    ) AS sumQty
FROM products

Now I have to subselects across the same table. So I think there must be a better way to create this query?

Abhishek Sharma · Accepted Answer · 2015-12-03 10:39:58Z

1

use join

Select *,count(quantity),sum(quantity)
From products p join 
product_has_part pp on pp.productsId = p.id
FROM products

or Group by use for count or sum for each id

Select *,count(quantity),sum(quantity)
From products p join 
product_has_part pp on pp.productsId = p.id
FROM products
Group by p.id

answered Dec 3, 2015 at 10:39

Abhishek Sharma

6,6241 gold badge17 silver badges20 bronze badges

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

1 Comment

Juan Carlos Oropeza Over a year ago

I forgot mysql isnt ansi, and group by p.id will work

Juan Carlos Oropeza · Accepted Answer · 2015-12-03 10:40:00Z

0

p.f1, p.f2, p.f3 mean each field of p.

select p.f1, p.f2, p.f3  ... count(php.quantity), sum(php.quantity)
from products p
join product_has_part php
  on p.productsId  = php.productsId 
group by  p.f1, p.f2, p.f3 ..

answered Dec 3, 2015 at 10:40

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

Multiple Subselects to same table in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related