Re: JOIN on a lookup table

Keith Worthington wrote:
> 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
>

Hi All,

Replying to myself with an idea for your review. ( That I will test
tomorrow morning. ;-) ) What if I do three separate LEFT JOINs? That
seems like brute force but it should work. I would be very interested
in hearing about a more elegant solution.

SELECT sales_inv_part.item_id,
sales_inv_part.acct_sales_gl_nmbr,
sales_inv_part.acct_sales_gl_name,
sales_inv_part.acct_inv_gl_nmbr,
sales_inv_part.acct_inv_gl_name,
sales_inv_part.acct_cogs_gl_nmbr
tbl_gl_account.description AS
acct_cogs_gl_name,
FROM (
SELECT sales_part.item_id,
sales_part.acct_sales_gl_nmbr,
sales_part.acct_sales_gl_name,
sales_part.acct_inv_gl_nmbr,
tbl_gl_account.description AS
acct_inv_gl_name,
sales_part.acct_cogs_gl_nmbr,
FROM (
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_item.cogs_gl_account AS
acct_cogs_gl_nmbr
FROM tbl_item
LEFT JOIN tbl_gl_account
ON ( tbl_item.sales_gl_account =
tbl_gl_account.account_id )
) AS sales_part
LEFT JOIN tbl_gl_account
ON ( sales_part.acct_inv_gl_nmbr =
tbl_gl_account.account_id )
) AS sales_inv_part
LEFT JOIN tbl_gl_account
ON ( sales_inv_part.acct_cogs_gl_nmbr =
tbl_gl_account.account_id )
ORDER BY item_id;

--
Kind Regards,
Keith