Using multiple left join in postgresql

Question

I am new to computer field and I am working on SQL. I am using multiple join queries in PostgreSql. I am trying to make inventory management system. I am having 3 tables as follow:

inventory_limit_stock: id(int, PK), inventory_id (varchar), product_id(int), min_stock_limit(int)
product_inventory: id(int, PK), inventory_id(int), product_id(int), in_stock(int)
other_inventory: id(int, PK), product_id, A(int), B(int), C(int)

So I want to show the list of products that are less in stock than minimum limit set in Table 1. inventory_id in Table 1 contains value either in number or 'A','B','C'. If it;s number, it refers to Table 2 otherwise refers to Table 1. So for example if inventory_id of Table 1 contains value 2, it will check in Table 2 and get the in_stock value. While if it contains value 'A' than it refers to Table 3 with column A and the value in it will be taken. I tried the following query but didn't worked for me.

SELECT ils.product_id as limit_prod, ils.inventory_id as limit_inv from inventory_limit_stock ils 
left join product_inventory as pi on pi.product_id = ils.product_id 
left join other_inventory oi on oi.product_id = ils.product_id 
where (COALESCE(ils.min_stock_limit,0) > COALESCE(SUM(pi.in_stock), SUM(oi.limit_inv), 0)) group by ils.product_id, ils.inventory_id

I know there are too many mistakes in query, any help would be appreciated. Also sorry for this long elaboration of question, this is my first question here, hope you all understand. Thanks.

EDIT

Table 1 - inventory_limit_stock

id | product_id | inventory_id | min_stock_limit
1  |    121     |      5       |       25
2  |    052     |      B       |       27
3  |    8       |      13      |       11
4  |    121     |      13      |       35

Table 2 - product_inventory

id | product_id | inventory_id | in_stock
1  |    121     |      5       |    42
2  |    742     |      15      |    12
3  |    8       |      13      |    09
4  |    121     |      13      |    25

Table 3 - other_inventory

id | product_id |  A  | B  | C
1  |    121     |  42 | 12 | 11
2  |    052     |  42 | 25 | 25

Result List (Should show list with products in_stock quantity < minimum limit)

product_id | inventory | minimum_stock_limit | in_stock_available
    8      |     13    |        11           |        9
   121     |     13    |        35           |        25
   052     |     B     |        27           |        25

It would help your question to show some sample data for the 3 tables involved. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 5, 2018 at 16:35
@TimBiegeleisen I added a data to the table for the reference. Thanks — Rkpat
– Rkpat, Commented Nov 5, 2018 at 17:02

ScaisEdge · Accepted Answer · 2018-11-05 16:34:55Z

1

For aggregated result you should use having and not where

SELECT ils.product_id as limit_prod
  , ils.inventory_id as limit_inv 
from inventory_limit_stock ils 
left join product_inventory as pi on pi.product_id = ils.product_id 
left join other_inventory oi on oi.product_id = ils.product_id 
group by ils.product_id, ils.inventory_id
HAVING (COALESCE(ils.min_stock_limit,0) >COALESCE(SUM(pi.quantity), SUM(oi.limit_inv), 0))

having clause work on the result of select .. where work on the raw values in table ..

answered Nov 5, 2018 at 16:34

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

2 Comments

ScaisEdge Over a year ago

In your sample .. you refer to table1, table2, table3 and in yyour query you use others table name . please updated your question and add a coherent sample .. and last what is the role of table3 is not clear to me

Rkpat Over a year ago

added the table name. Okay so last table is Other Inventory which consist of columns - id, product_id, A, B, C. This A, B, C columns refers as Inventory that refers to the inventory_id of Table 1. This A, B, C columns contains the value of in_stock products available in this particular inventory. So as stated in my example, in Table 1 I have a product with id '052' with inventory_id 'B', so it will refer to the table 3 with product _id '052' and column 'B'.

Collectives™ on Stack Overflow

Using multiple left join in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related