CODEWITHSUNDEEP
mysqlsubquery
bytecounter
bytecounter

Reputation: 133

Multiple Subselects to same table in mysql

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?

Upvotes: 1

Views: 409

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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 ..

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

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

Upvotes: 1

Related Questions