Re: JOIN on a lookup table
От | Keith Worthington |
---|---|
Тема | Re: JOIN on a lookup table |
Дата | |
Msg-id | 425491EF.6050001@NarrowPathInc.com обсуждение исходный текст |
Ответ на | JOIN on a lookup table ("Keith Worthington" <keithw@narrowpathinc.com>) |
Ответы |
Re: JOIN on a lookup table
(Sean Davis <sdavis2@mail.nih.gov>)
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления:
Следующее
От: "Greg Sabino Mullane"Дата:
Сообщение: Re: binding values to sql statement in DBI perl