Re: JOIN on a lookup table
От | Keith Worthington |
---|---|
Тема | Re: JOIN on a lookup table |
Дата | |
Msg-id | 20050407192050.M94738@narrowpathinc.com обсуждение исходный текст |
Ответ на | Re: JOIN on a lookup table (Bob Henkel <luckyratfoot@gmail.com>) |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления:
Следующее
От: brew@theMode.comДата:
Сообщение: Re: pg_restore returns error schema objects already exist