Re: JOIN on a lookup table

On Apr 6, 2005, at 9:50 PM, Keith Worthington wrote:

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

Keith,

What about something like:
SELECT tbl_item.id AS item_id,
sales.account as acct_sales_gl_nmbr,
sales.description as acct_sales_gl_name,
inventory.account as acct_inv_gl_nmbr,
inventory.description as acct_inv_gl_name,
cogs.account as acct_cogs_gl_nmbr,
cogs.description as acct_cogs_gl_nmbr,
FROM tbl_item,
(SELECT account_id as account,description
FROM tbl_gl_account
WHERE account_id=sales_gl_account) as sales,
(SELECT account_id as account,description
FROM tbl_gl_account
WHERE account_id=inventory_gl_account) as inventory,
(SELECT account_id as account,description
FROM tbl_gl_account
WHERE account_id=cogs_gl_account) as cogs
ORDER BY tbl_item.id;

Of course, make sure that sales_gl_account, inventory_gl_account,
cogs_gl_accoung, and account_id are indexed, etc. (Note that I didn't
test this, so syntax, etc. might be off a bit).

Sean

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly