Re: JOIN on a lookup table

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 *

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 tbl_gl_account.account_id *=* tbl_item.cogs_gl_account
AND bl_gl_account.account_id *=* tbl_item.inventory_gl_account
AND tbl_gl_account.account_id *=* tbl_item.sales_gl_account
ORDER BY tbl_item.id*;

On Apr 7, 2005 11:30 AM, Keith Worthington <keithw(at)narrowpathinc(dot)com> wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>