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