how to join 3 tables in postgres sql

Question

I have three tables

Item master: stores item master details

id integer NOT NULL DEFAULT, primary key
name character varying(255),
description character(255),
price double precision,
tax double precision,
readytosales character(1) DEFAULT 'N'::bpchar,
itemgroupid integer,
uom character varying(30),
quantity double precision DEFAULT 0,

purchase: stores purchase details

purchaseid integer NOT NULL DEFAULT,
quantity double precision DEFAULT 0,
purchasemasterid integer NOT NULL,
itemid integer NOT NULL,
itemprice double precision DEFAULT 0.00,

sales: stores sales details

salesid integer NOT NULL DEFAULT,
quantity double precision DEFAULT 0,
salesmasterid integer NOT NULL,
itemid integer,
itemprice double

formula used to get the stock summary is

itemmaster.quantity + purchase.quantity -sales.quantity

I used the following query to get the details, but couldn't get the results

select im.id as itemid, 
       name as itemname,
       im.quantity as oepningquantity, 
       im.price as openingprice,
       (im.quantity * im.price) as openingbalance,
       p.quantity as purchasequantity, p.itemprice as purchaseprice,
       (p.quantity * p.itemprice)as totalpurchaseprice, 
       s.quantity as salesquanity, s.itemprice as saleprice,
       (s.quantity *s.itemprice)as totalsalesprice
from item_master as im 
  full outer join purchase as p on im.id=p.itemid 
  full outer join sales as s on im.id=s.itemid

are you sure that item_master.itemgroupid doesn't correspond to the itemid in the other tables? your code looks solid, so it may be the contents of the tables and finding the right keys. — Matt
– Matt, Commented Dec 10, 2018 at 15:02
But couldn't get the results Would you elaborate more about the result you want? @PriyankaMasade you can use this to make formatted table ozh.github.io/ascii-tables — dwir182
– dwir182, Commented Dec 11, 2018 at 0:24

Rehan Shah · Accepted Answer · 2018-12-10 15:28:56Z

1

There is a tiny problem with your Query. Correct Query is :

   select im.id as itemid, 
   name as itemname,
   im.quantity as oepningquantity, 
   im.price as openingprice,
   (im.quantity * im.price) as openingbalance,
   p.quantity as purchasequantity, p.itemprice as purchaseprice,
   (p.quantity * p.itemprice)as totalpurchaseprice, 
   s.quantity as salesquanity, s.itemprice as saleprice,
   (s.quantity *s.itemprice)as totalsalesprice

_____from item_master as im ______ -------This Statement is Wrong.

   from item_master -- Try This One.

   full outer join purchase as p on im.id=p.itemid 
   full outer join sales as s on im.id=s.itemid

answered Dec 10, 2018 at 15:28

Rehan Shah

1,64716 silver badges33 bronze badges

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

Comments

Rehan Shah · Accepted Answer · 2018-12-10 15:33:39Z

1

For Example We have 3 tables. table_1. table_2. table_3.

and the table_2 and table_3 are reference by a foriegn key from table_1. so if we join these three table together the query will look like this.

Select {table 1 Cols} {table 2 Cols} {table 3 Cols}
from table_1
join table_1.table_2ID = table_2.Id on (Specify Condition here)
join table_1.table_3ID = table_3.Id on (Specify Condition here)

edited Dec 10, 2018 at 15:33

answered Dec 10, 2018 at 15:03

Rehan Shah

1,64716 silver badges33 bronze badges

Comments

Nikhil · Accepted Answer · 2018-12-10 15:38:00Z

0

this will work:

       select 
       im.id as itemid, 
       name as itemname,
       im.quantity as oepningquantity, 
       im.price as openingprice,
       (im.quantity * im.price) as openingbalance,
       p.quantity as purchasequantity,
       p.itemprice as purchaseprice,
       (p.quantity * p.itemprice)as totalpurchaseprice, 
       s.quantity as salesquanity, 
       s.itemprice as saleprice,
       (s.quantity *s.itemprice)as totalsalesprice
       from 
       Item master im,
       purchase p,
       sales s 
       where
       im.id=p.itemid and
       im.id=s.itemid;

answered Dec 10, 2018 at 15:38

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

Collectives™ on Stack Overflow

how to join 3 tables in postgres sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related