pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Query Optimisation required 2+ messages / 1 participants [nested] [flat]
* Query Optimisation required @ 2001-05-28 11:27 Kapil Tilwani <karan_pg_2@yahoo.com> 0 siblings, 0 replies; 2+ messages in thread From: Kapil Tilwani @ 2001-05-28 11:27 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Fw: Query Optimisation required @ 2001-07-17 16:14 Kapil Tilwani <karan_pg_2@yahoo.com> 0 siblings, 0 replies; 2+ messages in thread From: Kapil Tilwani @ 2001-07-17 16:14 UTC (permalink / raw) To: pgsql-sql; pgsql-general@postgresql.org ----- 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-07-17 16:14 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-05-28 11:27 Query Optimisation required Kapil Tilwani <karan_pg_2@yahoo.com> 2001-07-17 16:14 Fw: Query Optimisation required Kapil Tilwani <karan_pg_2@yahoo.com>
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