Multiple Joins? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Joins?

Author

Topic

maronism
Starting Member

2 Posts

Posted - 2002-01-24 : 00:49:40

I've been trying for a few days to accomplish this: I've got 3 tables...
Budget / gl_transactions / ExpenseCategories

I'm able to use joins to tie the ExpenseCategories (the text based description associated to a code used in the gl_transactions table). Now I'm trying to add budget entries that correspond to the same type of entries that are in the gl_transactions table.

I.e.: If there an entry for project=ABCABC, costcenter = 2A, etc. and there's a matching entry in the budget table, then I need to include that as well. Every variation I've tried just produces a huge number of results, basically matching everything in one table to the other. Any ideas?

- - - - - -
SELECT
B.Amount AS BudgAmt,
gl_transactions.project AS glPrj,
gl_transactions.account_number,
gl_transactions.cost_center_code,
gl_transactions.fund_number,
gl_transactions.fiscal_year,
gl_transactions.type_entry,
LEFT(gl_transactions.project, 3) AS L,
Desc1 = D1.Description,
RIGHT(gl_transactions.project, 3) AS R,
Desc2 = D2.Description,
gl_transactions.trans_desc_gl,
gl_transactions.trans_amount_gl

FROM gl_transactions
LEFT OUTER JOIN ExpenseCategories D1 ON LEFT(gl_transactions.project, 3) = D1.Category
AND D1.Season = '2002'
LEFT OUTER JOIN ExpenseCategories D2 ON RIGHT(gl_transactions.project, 3) = D2.Category
AND D2.Season = '2002' ,

Budget B RIGHT OUTER JOIN gl_transactions Q ON
B.project = Q.project
AND
B.fund = Q.fund_number
AND
B.costcenter = Q.cost_center_code
AND
B.Account = Q.account_number

WHERE
gl_transactions.account_number = '436750'
AND
gl_transactions.fiscal_year = '0102'

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-24 : 01:00:20

I think based on your description, you should be using INNER JOINs.

Outer joins will include all records from at least one table. Try you query with inner joins.

HTH
-Chad

maronism
Starting Member

2 Posts

Posted - 2002-01-24 : 01:07:56

Sorry, I do actually want results from the gl_transactions table to show up, even if there isn't a match in the ExpenseCategories table. The first joins work to accomplish that. The problem comes from when I then try to add the third table (Budget) and I again don't need an exact match using an Inner Join. Instead, I only need values from the Budget table if they exist as a match. If not, a NULL value is fine.

Nazim
A custom title

1408 Posts

Posted - 2002-01-24 : 01:14:59

i modified your query at your join on Budget . thought therez error with your query.

SELECT
B.Amount AS BudgAmt,
gl_transactions.project AS glPrj,
gl_transactions.account_number,
gl_transactions.cost_center_code,
gl_transactions.fund_number,
gl_transactions.fiscal_year,
gl_transactions.type_entry,
LEFT(gl_transactions.project, 3) AS L,
Desc1 = D1.Description,
RIGHT(gl_transactions.project, 3) AS R,
Desc2 = D2.Description,
gl_transactions.trans_desc_gl,
gl_transactions.trans_amount_gl
FROM gl_transactions
LEFT OUTER JOIN ExpenseCategories D1 ON LEFT(gl_transactions.project, 3) = D1.Category
AND D1.Season = '2002'
LEFT OUTER JOIN ExpenseCategories D2 ON RIGHT(gl_transactions.project, 3) = D2.Category
AND D2.Season = '2002'

left inner join
Budget B ON
B.project = gl_transactions.project
AND B.fund = gl_transactions.fund_number
AND B.costcenter = gl_transactions.cost_center_code
AND B.Account = gl_transactions.account_number
WHERE gl_transactions.account_number = '436750'
AND gl_transactions.fiscal_year = '0102'
[/b]
WHERE
gl_transactions.account_number = '436750'
AND
gl_transactions.fiscal_year = '0102'

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources