Re: JOIN on a lookup table

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