Re: JOIN on a lookup table - Mailing list pgsql-novice

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