Re: JOIN on a lookup table

Hi Keith

I think that something like this may be more simple ( if I understood
what you want to do ;) )

SELECT tbl_item.id AS item_id
, tbl_item.sales_gl_account AS acct_sales_gl_nmbr
, acct_sales.description AS acct_sales_gl_name
, tbl_item.inventory_gl_account AS acct_inv_gl_nmbr
, acct_inv.description AS acct_inv_gl_name
, tbl_item.cogs_gl_account AS acct_cogs_gl_nmbr
, acct_cogs.description AS acct_cogs_gl_name
FROM tbl_item LEFT OUTER JOIN tbl_gl_account acct_sales ON acct_sales.account_id = tbl_item.sales_gl_account
LEFT OUTER JOIN tbl_gl_account acct_inv ON acct_inv.account_id = tbl_item.inventory_gl_account
LEFT OUTER JOIN tbl_gl_account acct_cogs ON acct_cogs.account_id = tbl_item.cogs_gl_account
ORDER BY tbl_item.id;

Hope this help

Luiz

Keith Worthington escreveu:

>Hi All,
>
>I am working on a view that needs to join a table that holds lookup
>information. It is a fairly simple id vs name relationship. How can I get
>the different names I am looking for? Below is what I have for a query so far
>but obviously it isn't working. Any hints will be appreciated.
>
> SELECT tbl_item.id AS item_id,
> tbl_item.sales_gl_account AS acct_sales_gl_nmbr,
> tbl_gl_account.description AS acct_sales_gl_name,
> tbl_item.inventory_gl_account AS acct_inv_gl_nmbr,
> tbl_gl_account.description AS acct_inv_gl_name,
> tbl_item.cogs_gl_account AS acct_cogs_gl_nmbr,
> tbl_gl_account.description AS acct_cogs_gl_name
> FROM tbl_item
> JOIN tbl_gl_account
> ON ( account_id = sales_gl_account AND
> account_id = inventory_gl_account AND
> account_id = cogs_gl_account )
> ORDER BY tbl_item.id;
>
>Kind Regards,
>Keith
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>
>