Re: JOIN on a lookup table

On Thu, 7 Apr 2005 13:20:34 -0500, Bob Henkel wrote
> On Apr 7, 2005 1:07 PM, Keith Worthington  
> wrote: 
> > 
> > On Thu, 7 Apr 2005 12:50:20 -0400, Sean Davis wrote
> > > >
> > > > 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
> > > >
> > >
> > > Oops. My bad. Does moving the join outside the subselect do it?
> > > Something like:
> > >
> > > 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
> > > ) as sales_data,
> > > (
> > > SELECT tbl_gl_account.account_id AS account,
> > > tbl_gl_account.description
> > > FROM tbl_gl_account
> > > ) as inv_data,
> > > (
> > > SELECT tbl_gl_account.account_id AS account,
> > > tbl_gl_account.description
> > > FROM tbl_gl_account
> > > ) as cogs_data,
> > > WHERE sales_data.account=tbl_item.sales_gl_account AND
> > > cogs_data.account=tbl_item.cogs_gl_account AND
> > > inv_data.account =tbl_item.inventory_gl_account
> > > ORDER BY tbl_item.id;
> > >
> > > Sean
> > 
> > Sean,
> > 
> > No unfortunately it didn't. Now the whole thing aborts. :-( And I DID 
> > remove
> > the comma after cogs_data. ;-)
> > 
> > Kind Regards,
> > Keith
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://archives.postgresql.org
> >
> 
> Keith,
> Please send the SQL and the error that you said aborted.
> Thanks,
> Bob

Bob,

I must have made a fat finger mistake because I just recreated Sean's
suggested code and it worked fine.  Here is the final version based on his
suggestion.

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
       ) AS sales_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
       ) AS inv_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
       ) AS cogs_data
 WHERE sales_data.account = tbl_item.sales_gl_account
   AND inv_data.account   = tbl_item.inventory_gl_account
   AND cogs_data.account  = tbl_item.cogs_gl_account
ORDER BY tbl_item.id;

What I have discovered during this process is that only the brute force LEFT
JOIN solution that I came up with last night returns all records including
those that have no account numbers. :-(  I don't know if I have to use that
technique though as those records may not affect the desired output anyway.

The other thing that I have discovered is that according to EXPLAIN ANALYZE
all of these techniques result in basicly the same plan with Hash Joins for
each of the three references to tbl_gl_account.

Kind Regards,
Keith