From: | "Kapil Tilwani" <karan_pg_2(at)yahoo(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Fw: Query Optimisation required |
Date: | 2001-07-17 16:14:40 |
Message-ID: | 000401c10edf$cfd938e0$1300a8c0@kapil1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
----- Original Message -----
From: Kapil Tilwani
To: pgsql-sql(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | tamsin | 2001-07-17 16:20:52 | RE: How to find out the weekday from a date??? |
Previous Message | will trillich | 2001-07-17 16:11:56 | psql -l |
From | Date | Subject | |
---|---|---|---|
Next Message | Zot O'Connor | 2001-07-17 18:25:54 | Building RPMS with plperl (solution) |
Previous Message | Tom Lane | 2001-07-17 16:04:00 | Re: [SQL] epoch to show millseconds |