XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   Dear SQLers:   
      
        For a start, I decided to model how I do banking.  I have several   
   bank accounts, and I split these up for setasides.  When I list the   
   subaccounts, the order I want is first by the Accounts order, then the   
   Subaccounts order, and then the Subaccounts name.   
      
        The SQL below sets up my database and does what I want.  (Go to   
   the very end for the SELECT statement in question.)  Can I do this   
   (get at Accounts.ACOrd) in SQL Server without a join?  Did I define my   
   indexes properly?   
      
   ***** Start of Included Code *****   
   /* Banking Learning Database */   
   /* Version of 2011-04-01 14:42 */   
      
   /* Database Setup */   
      
   use master   
   go   
      
   drop database Banking   
   go   
      
   create database Banking   
   go   
      
   use Banking   
   go   
      
   /* Bank Accounts Table */   
      
   create table Accounts   
    (   
    ACUK nvarchar(4) primary key,     /* arbitrary length */   
    ACOrd int,   
    ACName nvarchar(40),     /* arbitrary length */   
    ACNr nvarchar(20)     /* arbitrary length */   
    )   
      
   create unique index ACUK on Accounts(ACUK)   
   create index ACOrd on Accounts(ACOrd,ACName)   
      
   /* Subaccounts Table */   
   /* Some are set-asides (S/A). */   
      
   create table Subaccounts   
    (   
    SAUK nvarchar(4) unique,     /* arbitrary length */   
    SAOrd int,   
    SAName nvarchar(40),     /* arbitrary length */   
    SAInACUK nvarchar(4),     /* arbitrary length */   
    foreign key (SAInACUK) references Accounts(ACUK)   
    )   
      
   create unique index SAUK on Subaccounts(SAUK)   
   create index SAOrd on Subaccounts(SAOrd,SAInACUK,SAName)   
      
   go   
      
   /* Inserts */   
      
   insert into Accounts   
    (ACUK,ACOrd,ACName,ACNr)   
   values   
    ('INC',10,'Income','12345-6 01'),   
    ('MAIN',20,'Main','12345-6 02'),   
    ('SAVE',30,'Savings','12345-6 11')   
      
   insert into Subaccounts   
    (SAUK,SAOrd,SAName,SAInACUK)   
   values   
    ('INC',10,'Income','INC'),   
    ('IBE',90,'Income Account Banking Expenses S/A','INC'),   
    ('IINT',95,'Income Account Interest','INC'),   
    ('PKT',10,'Pocket S/A','MAIN'),   
    ('RENT',20,'Rent S/A','MAIN'),   
    ('PH',30,'Phone S/A','MAIN'),   
    ('MBE',90,'Main Account Banking Expenses S/A','MAIN'),   
    ('MINT',95,'Main Account Interest','MAIN'),   
    ('RES',10,'Reserves','SAVE'),   
    ('ITSA',20,'Income Tax S/A','SAVE'),   
    ('SINT',95,'Savings Account Interest','SAVE')   
      
   /* Further Testing Inserts */   
      
   insert into Accounts   
    (ACUK,ACOrd,ACName,ACNr)   
   values   
    ('BOG',40,'Bogus','00000-0')   
      
   insert into Subaccounts   
    (SAUK,SAOrd,SAName,SAInACUK)   
   values   
    ('BOG2',20,'Bogus 2 S/A','BOG'),   
    ('BOG1',10,'Bogus 1 S/A','BOG')   
      
   go   
      
   /* Get results. */   
      
   select * from Accounts order by ACOrd,ACName   
      
   select Subaccounts.*,ACOrd   
   from Subaccounts   
   join Accounts on SAInACUK=ACUK   
   order by ACOrd,SAOrd,SAName   
   ***** End of Included Code *****   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)