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

>
> 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