Select statement that combines similar rows with certain ids?

Question

hi

I have a warehouse_products table which defines how many products in the warehouses

so lets say I have 20 records/rows in the table, some rows may contain the same product id but in a different warehouse

I need to create select statement that give every product one row, and in this row I must have the quantity in warehouse A and warehouse B ..

so in the end I will get for example 10 rows that contain all the data

Answer 1

Something like this:

SELECT id, domain_id, wh_product_id, SUM(item_bom.qty_bom) qty_total
FROM warehouse_products GROUP BY id, domain_id, wh_product_id

You won't be able to show warehouse_id. Nor wh_product_id, I would guess -- I included it here, but if have 20 rows instead of 10, remove it.

Answer 2

select p1.wh_id as whA_id, p2.wh_id as whB_id, 
    coalesce(p1.p_id, p2.p_id) as p_id, p1.qty as qty_A, 
    p2.qty as qty_B, coalesce(p1.enabled, p2.enabled) as enabled
from warehouse_products p1
full outer join warehouse_products p2 on p1.p_id = p2.p_id
    and p1.wh_id = 'A' and p2.wh_id = 'B'

Update:

I forgot that MySql does not support FULL OUTER JOIN. I don't have time to update the query right now, but there are examples of how to get the same results here.