Re: JOIN on a lookup table

On Apr 7, 2005 1:07 PM, Keith Worthington <keithw@narrowpathinc.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

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