pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kapil Tilwani <karan_pg_2@yahoo.com>
To: pgsql-sql@postgresql.org
To: pgsql-general@postgresql.org
Subject: Fw: Query Optimisation required
Date: Tue, 17 Jul 2001 21:44:40 +0530
Message-ID: <000401c10edf$cfd938e0$1300a8c0@kapil1> (raw)


----- Original Message ----- 
From: Kapil Tilwani 
To: pgsql-sql@postgresql.org 
Sent: Monday, May 28, 2001 4:57 PM
Subject: Query Optimisation required


Hi,

What I have right now is not directly a Postgres question, but more of a sql question for MS-Access and MS-SQL.
The application is a small Inventory Management Module in which there are 7 tables
 
Master Tables
1. ItemMaster containing the ItemID as the primary key
Transaction Tables    -    All of these tables are having a corresponding Transaction Master tables
2. PurchaseDetails , (e.g., child of PurchaseMaster)
3. SalesDetails
4. PurRetDetails (Purchase Returns)
5. SalesRetDetails (Sales Returns)
6 & 7. OtherIssues and OtherReceipts
 
ItemMaster            PurchaseDetails                 SalesDetails                    PurReturns        SalesRet
==========            ================            ============                   ==========       ========
ItemID (PK)              PurDetailsID (PK)            SalesDetailsID (PK)        PurRetID (PK)    SalesRetID (PK)
ItemName                ItemID (FK)                     ItemID (FK)                    ItemID (FK)       ItemID (FK)
.                               Qty                                  Qty                                Qty                    Qty
.                                .                                        .                                    .                        .
Stock                        .                                        .                                    .                        .

Similarly, there is otherissues and otherreceipts tables.

The thing is that the ItemID need not exist in all tables, so I need a Left Join.  Though explained step-wise below, it is to be done by one query.
ItemMaster -> (Left Join) -> PurchaseDetails = ResultA (Add Qty to Stock)
ResultA -> (Left Join) -> SalesDetails = ResultB (Reduce Qty, etc)
ResultB ->  (Left Join) -> PurReturns = ResultC   
ResultC ->  (Left Join) -> SalesRet = ResultD
..........

Currently, I have created independent views (queries), which left joins ItemMaster with each of the transaction tables and then one query which equi-joins each of the queries because of problems in Multiple Left-joins from one table in MS-Access (all RDBMSs give that prob ???)

The syntax I need should be so generic that if I am moving from MS-Access to MySQL or Postgres, I need not modify the code in my VB Application.
****I need this query for MS-Access, MS-SQL and Postgres.  Because the application is supposed to be such that for upto 2-3 users, the application would be running on MS-Access or MSDE, while for heavier databases i.e., greater than 4 , the ideal database would be Postgres.

Thankx a lot
Kapil

view thread (2+ messages)

Message-ID: <000401c10edf$cfd938e0$1300a8c0@kapil1>
Permalink:  ../000401c10edf$cfd938e0$1300a8c0@kapil1/
Also on:    postgresql.org/message-id/000401c10edf$cfd938e0$1300a8c0@kapil1

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: karan_pg_2@yahoo.com, pgsql-general@postgresql.org
  Subject: Re: Fw: Query Optimisation required
  In-Reply-To: <000401c10edf$cfd938e0$1300a8c0@kapil1>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox