Re: JOIN on a lookup table

On Thu, 7 Apr 2005 06:19:51 -0400, Sean Davis wrote
> On Apr 6, 2005, at 9:50 PM, Keith Worthington wrote:
> 
> > Keith Worthington wrote:
> >> Hi All,
> >> I am working on a view that needs to join a table that holds lookup
> >> information.  It is a fairly simple id vs name relationship.  How can 
> >> I get
> >> the different names I am looking for?  Below is what I have for a 
> >> query so far
> >> but obviously it isn't working.  Any hints will be appreciated.
> >>    SELECT tbl_item.id                   AS item_id,
> >>           tbl_item.sales_gl_account     AS acct_sales_gl_nmbr,
> >>           tbl_gl_account.description    AS acct_sales_gl_name,
> >>           tbl_item.inventory_gl_account AS acct_inv_gl_nmbr,
> >>           tbl_gl_account.description    AS acct_inv_gl_name,
> >>           tbl_item.cogs_gl_account      AS acct_cogs_gl_nmbr,
> >>           tbl_gl_account.description    AS acct_cogs_gl_name
> >>      FROM tbl_item
> >>      JOIN tbl_gl_account
> >>        ON ( account_id = sales_gl_account AND
> >>             account_id = inventory_gl_account AND
> >>             account_id = cogs_gl_account )
> >>   ORDER BY tbl_item.id;
> >> Kind Regards,
> >> Keith
> >> ---------------------------(end of 
> >> broadcast)---------------------------
> >> TIP 7: don't forget to increase your free space map settings
> >
> > Hi All,
> >
> > Replying to myself with an idea for your review.  ( That I will test 
> > tomorrow morning. ;-) )  What if I do three separate LEFT JOINs?  That 
> > seems like brute force but it should work.  I would be very interested 
> > in hearing about a more elegant solution.
> >
> > SELECT sales_inv_part.item_id,
> >        sales_inv_part.acct_sales_gl_nmbr,
> >        sales_inv_part.acct_sales_gl_name,
> >        sales_inv_part.acct_inv_gl_nmbr,
> >        sales_inv_part.acct_inv_gl_name,
> >        sales_inv_part.acct_cogs_gl_nmbr
> >        tbl_gl_account.description AS
> >           acct_cogs_gl_name,
> >   FROM (
> >          SELECT sales_part.item_id,
> >                 sales_part.acct_sales_gl_nmbr,
> >                 sales_part.acct_sales_gl_name,
> >                 sales_part.acct_inv_gl_nmbr,
> >                 tbl_gl_account.description AS
> >                    acct_inv_gl_name,
> >                 sales_part.acct_cogs_gl_nmbr,
> >            FROM (
> >                   SELECT tbl_item.id AS
> >                             item_id,
> >                          tbl_item.sales_gl_account AS
> >                             acct_sales_gl_nmbr,
> >                          tbl_gl_account.description AS
> >                             acct_sales_gl_name,
> >                          tbl_item.inventory_gl_account AS
> >                             acct_inv_gl_nmbr,
> >                          tbl_item.cogs_gl_account AS
> >                             acct_cogs_gl_nmbr
> >                     FROM tbl_item
> >                     LEFT JOIN tbl_gl_account
> >                       ON ( tbl_item.sales_gl_account =
> >                            tbl_gl_account.account_id )
> >                 ) AS sales_part
> >            LEFT JOIN tbl_gl_account
> >              ON ( sales_part.acct_inv_gl_nmbr =
> >                   tbl_gl_account.account_id )
> >        ) AS sales_inv_part
> >   LEFT JOIN tbl_gl_account
> >     ON ( sales_inv_part.acct_cogs_gl_nmbr =
> >          tbl_gl_account.account_id )
> >  ORDER BY item_id;
> >
> > -- 
> > Kind Regards,
> > Keith
> >
> 
> Keith,
> 
> What about something like:
> SELECT tbl_item.id AS item_id,
> 	sales.account as acct_sales_gl_nmbr,
> 	sales.description as acct_sales_gl_name,
> 	inventory.account as acct_inv_gl_nmbr,
> 	inventory.description as acct_inv_gl_name,
> 	cogs.account as acct_cogs_gl_nmbr,
> 	cogs.description as acct_cogs_gl_nmbr,
> FROM tbl_item,
> 	(SELECT account_id as account,description
> 	 FROM   tbl_gl_account
> 	 WHERE  account_id=sales_gl_account) as sales,
> 	(SELECT account_id as account,description
> 	 FROM   tbl_gl_account
> 	 WHERE  account_id=inventory_gl_account) as inventory,
> 	(SELECT account_id as account,description
> 	 FROM   tbl_gl_account
> 	 WHERE  account_id=cogs_gl_account) as cogs
> ORDER BY tbl_item.id;
> 
> Of course, make sure that sales_gl_account, inventory_gl_account, 
> cogs_gl_accoung, and account_id are indexed, etc.  (Note that I 
> didn't test this, so syntax, etc. might be off a bit).
> 
> Sean

Sean,

I tried to implement your suggestion to see how it performed vs the other
solutions but couldn't get it to work.  Here is what I tried:

SELECT tbl_item.id AS item_id,
       sales_data.account AS acct_sales_gl_nmbr,
       sales_data.description AS acct_sales_gl_name,
       inv_data.account AS acct_inv_gl_nmbr,
       inv_data.description AS acct_inv_gl_name,
       cogs_data.account AS acct_cogs_gl_nmbr,
       cogs_data.description AS acct_cogs_gl_name
  FROM tbl_item,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
          WHERE tbl_gl_account.account_id =
                tbl_item.sales_gl_account
       ) AS sales_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
          WHERE tbl_gl_account.account_id =
                tbl_item.inventory_gl_account
       ) AS inv_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
          WHERE tbl_gl_account.account_id =
                tbl_item.cogs_gl_account
       ) AS cogs_data
ORDER BY tbl_item.id;

And this is the error message that I got:
ERROR:  subquery in FROM may not refer to other relations of same query level

Kind Regards,
Keith Worthington
President

Narrow Path, Inc.
520 Trumbull Highway
Lebanon, CT 06249-1424
Telephone:  (860) 642-7114
Facsimile:  (860) 642-7290
Mobile:     (860) 608-6101