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@postgresql.org so that your
>      message can get through to the mailing list cleanly