Is it possible to get some result like this from 2 tables: ps: warehouse 1,... I mean all of my warehouses

right now im doing a for loop and getting data for each sku like this:

first getting all products from products table:

SELECT id, sku FROM products

then do a for loop on this:

SELECT ware.id, ware.name, ware_inv.available as inventory,
ware_inv.id as inventoryName, ware.country
FROM warehouses as ware JOIN warehouse_inventory as ware_inv
ON ware.id = ware_inv.warehouse_id
WHERE ware.store_id = $1
AND ware_inv.sku = $2

warehouse table schema:

warehouse inventory table schema:

warehouse_id inside warehouse_inventory is foreign key to warehouses table.

CodePudding user response：

A conditional aggregation is one way to pivot that'll work in most RDBMS.

SELECT product.sku, warehouse.store_id
, SUM(CASE WHEN warehouse.name = 'warehouse 1' THEN inventory.available END) AS "warehouse 1"
, SUM(CASE WHEN warehouse.name = 'warehouse 2' THEN inventory.available END) AS "warehouse 2"
, SUM(CASE WHEN warehouse.name = 'warehouse 3' THEN inventory.available END) AS "warehouse 3"
, SUM(CASE WHEN warehouse.name = 'warehouse 4' THEN inventory.available END) AS "warehouse 4"
, SUM(inventory.available) AS "available"
FROM products AS product
LEFT JOIN warehouse_inventory AS inventory 
  ON inventory.sku = product.sku
LEFT JOIN warehouses AS warehouse
  ON warehouse.id = inventory.warehouse_id
WHERE product.sku = $2
  AND (warehouse.store_id = $1 OR warehouse.id IS NULL)
GROUP BY product.sku, warehouse.store_id

Because it left joins on the products, this would even show sku's that aren't available in any of the warehouses.