Thread: JOIN on a lookup table

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

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

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

On Thu, 7 Apr 2005 06:19:51 -0400, Sean Davis wrote
> 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
> >
> > 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
> >
>
> 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

Sean,

I tried to implement your suggestion to see how it performed vs the other
solutions but couldn't get it to work.  Here is what I tried:

SELECT tbl_item.id AS item_id,
       sales_data.account AS acct_sales_gl_nmbr,
       sales_data.description AS acct_sales_gl_name,
       inv_data.account AS acct_inv_gl_nmbr,
       inv_data.description AS acct_inv_gl_name,
       cogs_data.account AS acct_cogs_gl_nmbr,
       cogs_data.description AS acct_cogs_gl_name
  FROM tbl_item,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
          WHERE tbl_gl_account.account_id =
                tbl_item.sales_gl_account
       ) AS sales_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
          WHERE tbl_gl_account.account_id =
                tbl_item.inventory_gl_account
       ) AS inv_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
          WHERE tbl_gl_account.account_id =
                tbl_item.cogs_gl_account
       ) AS cogs_data
ORDER BY tbl_item.id;

And this is the error message that I got:
ERROR:  subquery in FROM may not refer to other relations of same query level

Kind Regards,
Keith Worthington
President

Narrow Path, Inc.
520 Trumbull Highway
Lebanon, CT 06249-1424
Telephone:  (860) 642-7114
Facsimile:  (860) 642-7290
Mobile:     (860) 608-6101

>
> Sean,
>
> I tried to implement your suggestion to see how it performed vs the
> other
> solutions but couldn't get it to work.  Here is what I tried:
>
> SELECT tbl_item.id AS item_id,
>        sales_data.account AS acct_sales_gl_nmbr,
>        sales_data.description AS acct_sales_gl_name,
>        inv_data.account AS acct_inv_gl_nmbr,
>        inv_data.description AS acct_inv_gl_name,
>        cogs_data.account AS acct_cogs_gl_nmbr,
>        cogs_data.description AS acct_cogs_gl_name
>   FROM tbl_item,
>        (
>          SELECT tbl_gl_account.account_id AS account,
>                 tbl_gl_account.description
>            FROM tbl_gl_account
>           WHERE tbl_gl_account.account_id =
>                 tbl_item.sales_gl_account
>        ) AS sales_data,
>        (
>          SELECT tbl_gl_account.account_id AS account,
>                 tbl_gl_account.description
>            FROM tbl_gl_account
>           WHERE tbl_gl_account.account_id =
>                 tbl_item.inventory_gl_account
>        ) AS inv_data,
>        (
>          SELECT tbl_gl_account.account_id AS account,
>                 tbl_gl_account.description
>            FROM tbl_gl_account
>           WHERE tbl_gl_account.account_id =
>                 tbl_item.cogs_gl_account
>        ) AS cogs_data
> ORDER BY tbl_item.id;
>
> And this is the error message that I got:
> ERROR:  subquery in FROM may not refer to other relations of same
> query level
>

Oops.  My bad.  Does moving the join outside the subselect do it?
Something like:


SELECT tbl_item.id AS item_id,
        sales_data.account AS acct_sales_gl_nmbr,
        sales_data.description AS acct_sales_gl_name,
        inv_data.account AS acct_inv_gl_nmbr,
        inv_data.description AS acct_inv_gl_name,
        cogs_data.account AS acct_cogs_gl_nmbr,
        cogs_data.description AS acct_cogs_gl_name
   FROM tbl_item,
        (
          SELECT tbl_gl_account.account_id AS account,
                 tbl_gl_account.description
            FROM tbl_gl_account
        ) as sales_data,
        (
          SELECT tbl_gl_account.account_id AS account,
                 tbl_gl_account.description
            FROM tbl_gl_account
        ) as inv_data,
        (
          SELECT tbl_gl_account.account_id AS account,
                 tbl_gl_account.description
            FROM tbl_gl_account
        ) as cogs_data,
   WHERE sales_data.account=tbl_item.sales_gl_account AND
       cogs_data.account=tbl_item.cogs_gl_account AND
       inv_data.account =tbl_item.inventory_gl_account
   ORDER BY tbl_item.id;

Sean

Can you have a correlated sub query in an inline view like you are trying to do? I have never tried this and don't have a DB to test this on at the moment.  I'm thinking something more like this

On Thu, 7 Apr 2005 12:50:20 -0400, Sean Davis wrote
> >
> > Sean,
> >
> > I tried to implement your suggestion to see how it performed vs the
> > other
> > solutions but couldn't get it to work.  Here is what I tried:
> >
> > SELECT tbl_item.id AS item_id,
> >        sales_data.account AS acct_sales_gl_nmbr,
> >        sales_data.description AS acct_sales_gl_name,
> >        inv_data.account AS acct_inv_gl_nmbr,
> >        inv_data.description AS acct_inv_gl_name,
> >        cogs_data.account AS acct_cogs_gl_nmbr,
> >        cogs_data.description AS acct_cogs_gl_name
> >   FROM tbl_item,
> >        (
> >          SELECT tbl_gl_account.account_id AS account,
> >                 tbl_gl_account.description
> >            FROM tbl_gl_account
> >           WHERE tbl_gl_account.account_id =
> >                 tbl_item.sales_gl_account
> >        ) AS sales_data,
> >        (
> >          SELECT tbl_gl_account.account_id AS account,
> >                 tbl_gl_account.description
> >            FROM tbl_gl_account
> >           WHERE tbl_gl_account.account_id =
> >                 tbl_item.inventory_gl_account
> >        ) AS inv_data,
> >        (
> >          SELECT tbl_gl_account.account_id AS account,
> >                 tbl_gl_account.description
> >            FROM tbl_gl_account
> >           WHERE tbl_gl_account.account_id =
> >                 tbl_item.cogs_gl_account
> >        ) AS cogs_data
> > ORDER BY tbl_item.id;
> >
> > And this is the error message that I got:
> > ERROR:  subquery in FROM may not refer to other relations of same
> > query level
> >
>
> Oops.  My bad.  Does moving the join outside the subselect do it?
> Something like:
>
> SELECT tbl_item.id AS item_id,
>         sales_data.account AS acct_sales_gl_nmbr,
>         sales_data.description AS acct_sales_gl_name,
>         inv_data.account AS acct_inv_gl_nmbr,
>         inv_data.description AS acct_inv_gl_name,
>         cogs_data.account AS acct_cogs_gl_nmbr,
>         cogs_data.description AS acct_cogs_gl_name
>    FROM tbl_item,
>         (
>           SELECT tbl_gl_account.account_id AS account,
>                  tbl_gl_account.description
>             FROM tbl_gl_account
>         ) as sales_data,
>         (
>           SELECT tbl_gl_account.account_id AS account,
>                  tbl_gl_account.description
>             FROM tbl_gl_account
>         ) as inv_data,
>         (
>           SELECT tbl_gl_account.account_id AS account,
>                  tbl_gl_account.description
>             FROM tbl_gl_account
>         ) as cogs_data,
>    WHERE sales_data.account=tbl_item.sales_gl_account AND
>        cogs_data.account=tbl_item.cogs_gl_account AND
>        inv_data.account =tbl_item.inventory_gl_account
>    ORDER BY tbl_item.id;
>
> Sean

Sean,

No unfortunately it didn't.  Now the whole thing aborts. :-(  And I DID remove
the comma after cogs_data. ;-)

Kind Regards,
Keith

On Thu, 7 Apr 2005 13:20:34 -0500, Bob Henkel wrote
> On Apr 7, 2005 1:07 PM, Keith Worthington <keithw@narrowpathinc.com>
> wrote:
> >
> > On Thu, 7 Apr 2005 12:50:20 -0400, Sean Davis wrote
> > > >
> > > > Sean,
> > > >
> > > > I tried to implement your suggestion to see how it performed vs the
> > > > other
> > > > solutions but couldn't get it to work. Here is what I tried:
> > > >
> > > > SELECT tbl_item.id AS item_id,
> > > > sales_data.account AS acct_sales_gl_nmbr,
> > > > sales_data.description AS acct_sales_gl_name,
> > > > inv_data.account AS acct_inv_gl_nmbr,
> > > > inv_data.description AS acct_inv_gl_name,
> > > > cogs_data.account AS acct_cogs_gl_nmbr,
> > > > cogs_data.description AS acct_cogs_gl_name
> > > > FROM tbl_item,
> > > > (
> > > > SELECT tbl_gl_account.account_id AS account,
> > > > tbl_gl_account.description
> > > > FROM tbl_gl_account
> > > > WHERE tbl_gl_account.account_id =
> > > > tbl_item.sales_gl_account
> > > > ) AS sales_data,
> > > > (
> > > > SELECT tbl_gl_account.account_id AS account,
> > > > tbl_gl_account.description
> > > > FROM tbl_gl_account
> > > > WHERE tbl_gl_account.account_id =
> > > > tbl_item.inventory_gl_account
> > > > ) AS inv_data,
> > > > (
> > > > SELECT tbl_gl_account.account_id AS account,
> > > > tbl_gl_account.description
> > > > FROM tbl_gl_account
> > > > WHERE tbl_gl_account.account_id =
> > > > tbl_item.cogs_gl_account
> > > > ) AS cogs_data
> > > > ORDER BY tbl_item.id;
> > > >
> > > > And this is the error message that I got:
> > > > ERROR: subquery in FROM may not refer to other relations of same
> > > > query level
> > > >
> > >
> > > Oops. My bad. Does moving the join outside the subselect do it?
> > > Something like:
> > >
> > > SELECT tbl_item.id AS item_id,
> > > sales_data.account AS acct_sales_gl_nmbr,
> > > sales_data.description AS acct_sales_gl_name,
> > > inv_data.account AS acct_inv_gl_nmbr,
> > > inv_data.description AS acct_inv_gl_name,
> > > cogs_data.account AS acct_cogs_gl_nmbr,
> > > cogs_data.description AS acct_cogs_gl_name
> > > FROM tbl_item,
> > > (
> > > SELECT tbl_gl_account.account_id AS account,
> > > tbl_gl_account.description
> > > FROM tbl_gl_account
> > > ) as sales_data,
> > > (
> > > SELECT tbl_gl_account.account_id AS account,
> > > tbl_gl_account.description
> > > FROM tbl_gl_account
> > > ) as inv_data,
> > > (
> > > SELECT tbl_gl_account.account_id AS account,
> > > tbl_gl_account.description
> > > FROM tbl_gl_account
> > > ) as cogs_data,
> > > WHERE sales_data.account=tbl_item.sales_gl_account AND
> > > cogs_data.account=tbl_item.cogs_gl_account AND
> > > inv_data.account =tbl_item.inventory_gl_account
> > > ORDER BY tbl_item.id;
> > >
> > > Sean
> >
> > Sean,
> >
> > No unfortunately it didn't. Now the whole thing aborts. :-( And I DID
> > remove
> > the comma after cogs_data. ;-)
> >
> > Kind Regards,
> > Keith
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
> Keith,
> Please send the SQL and the error that you said aborted.
> Thanks,
> Bob

Bob,

I must have made a fat finger mistake because I just recreated Sean's
suggested code and it worked fine.  Here is the final version based on his
suggestion.

SELECT tbl_item.id AS item_id,
       sales_data.account AS acct_sales_gl_nmbr,
       sales_data.description AS acct_sales_gl_name,
       inv_data.account AS acct_inv_gl_nmbr,
       inv_data.description AS acct_inv_gl_name,
       cogs_data.account AS acct_cogs_gl_nmbr,
       cogs_data.description AS acct_cogs_gl_name
  FROM tbl_item,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
       ) AS sales_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
       ) AS inv_data,
       (
         SELECT tbl_gl_account.account_id AS account,
                tbl_gl_account.description
           FROM tbl_gl_account
       ) AS cogs_data
 WHERE sales_data.account = tbl_item.sales_gl_account
   AND inv_data.account   = tbl_item.inventory_gl_account
   AND cogs_data.account  = tbl_item.cogs_gl_account
ORDER BY tbl_item.id;

What I have discovered during this process is that only the brute force LEFT
JOIN solution that I came up with last night returns all records including
those that have no account numbers. :-(  I don't know if I have to use that
technique though as those records may not affect the desired output anyway.

The other thing that I have discovered is that according to EXPLAIN ANALYZE
all of these techniques result in basicly the same plan with Hash Joins for
each of the three references to tbl_gl_account.

Kind Regards,
Keith

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