Re: JOIN on a lookup table

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