0

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

enter image description here

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:

enter image description here

warehouse inventory table schema:

enter image description here

warehouse_id inside warehouse_inventory is foreign key to warehouses table.

Mj Ebrahimzadeh
  • 587
  • 9
  • 22
  • 1
    The question is not clear, your query currently returns the columns ->ware.id, ware.name, ware_inv.available as inventory, ware_inv.id as inventoryName, ware.country but the expected output is different?. can you show what is the expected output and the values from warehouse and warehouse_inventory – George Joseph Nov 11 '21 at 11:50
  • @GeorgeJoseph Sorry my bad. Yes first I get all my products from products table and then im getting all warehouses data for each sku inside a for loop. – Mj Ebrahimzadeh Nov 11 '21 at 12:00
  • you have to share the expected output and the data from all of the tables-> products, warehouse,warehouse_inventory – George Joseph Nov 11 '21 at 12:05
  • [Images](//meta.stackoverflow.com/q/285551/90527) should not be used for textual data, such as tables and results. Since SQL includes data definition, a [mcve] should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than ad hoc table specifications or images) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Sample results don't need to be code. – outis Nov 12 '21 at 03:32
  • See the help center for more on [how to ask good questions](//stackoverflow.com/help/how-to-ask) and many other helpful topics. – outis Nov 12 '21 at 03:32
  • Part of why this question isn't clear is that "for-loop" isn't a concept in relational databases. The question would be made clearer if it were written in terms of relational database concepts. – outis Nov 12 '21 at 03:35
  • Duplicate of "[SQL Transpose Rows as Columns](https://stackoverflow.com/q/2099198/90527)" and many other questions about pivots, cross-tabs and the like. – outis Nov 12 '21 at 03:51

1 Answers1

1

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.

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • Other methods to pivot in postgresql are [crosstab](https://stackoverflow.com/q/3002499/4003419) and [filter](https://stackoverflow.com/a/68191237/4003419) – LukStorms Nov 11 '21 at 12:47