Re: JOIN on a lookup table

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