Re: JOIN on a lookup table

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