Re: JOIN on a lookup table

On Apr 7, 2005 1:20 PM, Bob Henkel <luckyratfoot(at)gmail(dot)com> wrote:

>
>
> On Apr 7, 2005 1:07 PM, Keith Worthington <keithw(at)narrowpathinc(dot)com>
> 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
>

You could make the SQL script smaller by writting it as
*

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*,*
tbl_gl_account*,*
tbl_gl_account*,*
tbl_gl_account
WHERE tbl_gl_account.account_id *=* tbl_item.cogs_gl_account
AND bl_gl_account.account_id *=* tbl_item.inventory_gl_account
AND tbl_gl_account.account_id *=* tbl_item.sales_gl_account
ORDER BY tbl_item.id*;

Though both statements should give you same result