Re: JOIN on a lookup table - Mailing list pgsql-novice

From Bob Henkel
Subject Re: JOIN on a lookup table
Date
Msg-id 762e5c050407114444c2b063@mail.gmail.com
Whole thread Raw
In response to Re: JOIN on a lookup table  (Bob Henkel <luckyratfoot@gmail.com>)
List pgsql-novice
On Apr 7, 2005 1:20 PM, Bob Henkel <luckyratfoot@gmail.com> wrote:


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

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

pgsql-novice by date:

Previous
From: Bob Henkel
Date:
Subject: Re: JOIN on a lookup table
Next
From: "Keith Worthington"
Date:
Subject: Re: JOIN on a lookup table