Received: from smtp011.mail.yahoo.com (smtp011.mail.yahoo.com [216.136.173.31]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5T7XEa80128 for ; Fri, 29 Jun 2001 03:33:15 -0400 (EDT) (envelope-from karan_pg_2@yahoo.com) Received: from dialpool-210-214-32-101.maa.sify.net (HELO kapil1) (210.214.32.101) by smtp.mail.vip.sc5.yahoo.com with SMTP; 29 Jun 2001 07:33:10 -0000 X-Apparently-From: Message-ID: <002001c0e812$35275780$1300a8c0@kapil1> From: "Kapil Tilwani" To: Subject: Query Optimisation required Date: Mon, 28 May 2001 16:57:09 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0128_01C0E797.3BDEA9A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200106/463 X-Sequence-Number: 3766 This is a multi-part message in MIME format. ------=_NextPart_000_0128_01C0E797.3BDEA9A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, What I have right now is not directly a Postgres question, but more of a sq= l question for MS-Access and MS-SQL. The application is a small Inventory Management Module in which there are 7= tables =20 Master Tables 1. ItemMaster containing the ItemID as the primary key Transaction Tables - All of these tables are having a corresponding T= ransaction Master tables 2. PurchaseDetails , (e.g., child of PurchaseMaster) 3. SalesDetails 4. PurRetDetails (Purchase Returns) 5. SalesRetDetails (Sales Returns) 6 & 7. OtherIssues and OtherReceipts =20 ItemMaster PurchaseDetails SalesDetails = PurReturns SalesRet =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D 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 =3D ResultA (Add Qty to Stock) ResultA -> (Left Join) -> SalesDetails =3D ResultB (Reduce Qty, etc) ResultB -> (Left Join) -> PurReturns =3D ResultC=20=20=20 ResultC -> (Left Join) -> SalesRet =3D ResultD .......... Currently, I have created independent views (queries), which left joins Ite= mMaster with each of the transaction tables and then one query which equi-j= oins each of the queries because of problems in Multiple Left-joins from on= e 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 t= o 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 appl= ication is supposed to be such that for upto 2-3 users, the application wou= ld be running on MS-Access or MSDE, while for heavier databases i.e., great= er than 4 , the ideal database would be Postgres. Thankx a lot Kapil ------=_NextPart_000_0128_01C0E797.3BDEA9A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
What I have right now is not dir= ectly a=20 Postgres question, but more of a sql question for MS-Access and=20 MS-SQL.
The application is a small Inven= tory=20 Management Module in which there are 7 tables
 
Master Tables
1. ItemMaster containing the Ite= mID as=20 the primary key
Transaction Tables  &n= bsp;=20 -    All of these tables are having a corresponding Transact= ion=20 Master tables
2. PurchaseDetails , (e.g.,= child of=20 PurchaseMaster)
3. SalesDetails
4. PurRetDetails (Purchase=20 Returns)
5. SalesRetDetails (Sales=20 Returns)
6 & 7. OtherIssues and=20 OtherReceipts
 
ItemMaster   =20         PurchaseDetails   &nbs= p;=20            =20 SalesDetails           =20         PurReturns   =20     SalesRet
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D&n= bsp;  =20         =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D   =20        =20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D      &nb= sp;            = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D       =3D= =3D=3D=3D=3D=3D=3D=3D
ItemID (PK)        &n= bsp;     PurDetailsID=20 (PK)            SalesDetailsID= =20 (PK)        PurRetID (PK)   = =20 SalesRetID (PK)
ItemName   =20             ItemID=20 (FK)              &n= bsp;=20      ItemID (FK)       = =20             ItemID=20 (FK)       ItemID (FK)
.           = ;            &n= bsp;       Qty    &n= bsp;            = ;            &n= bsp;    Qty   =20                = =20             Qty  &nb= sp;=20                = =20 Qty
.      =  =20                = =20         .       = ;=20                = =20                = =20 .               = ;=20                = =20     .           = ;=20             .
Stock          &= nbsp;           &nbs= p;=20 .               = ;=20                = =20         .       = ;=20                = =20             .   = ;=20                = =20     .
 
Similarly, there is otherissues = and=20 otherreceipts tables.
 
The thing is that the ItemID nee= d not=20 exist in all tables, so I need a Left Join.  Though explained step-wis= e=20 below, it is to be done by one query.
ItemMaster -> (Left Join) -&g= t;=20 PurchaseDetails =3D ResultA (Add Qty to Stock)
ResultA -> (Left Join) ->= =20 SalesDetails =3D ResultB (Reduce Qty, etc)
ResultB ->  (Left Join) = ->=20 PurReturns =3D ResultC   
ResultC ->  (Left Join)= =20 -> SalesRet =3D ResultD
..........
 
Currently, I have created independent views (queries), which left join= s=20 ItemMaster with each of the transaction tables and then one query which=20 equi-joins each of the queries because of problems in Multiple Left-joins f= rom=20 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-Acc= ess=20 to MySQL or Postgres, I need not modify the code in my VB Application.
****I need this query for MS-Acc= ess,=20 MS-SQL and Postgres.  Because the application is supposed to be such t= hat=20 for upto 2-3 users, the application would be running on MS-Access or MSDE, = while=20 for heavier databases i.e., greater than 4 , the ideal database would be=20 Postgres.
 
Thankx a lot
Kapil
------=_NextPart_000_0128_01C0E797.3BDEA9A0-- _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com