Join Issue - 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 2005 Forums

Transact-SQL (2005)

Join Issue

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2008-09-26 : 10:51:06

I have this query...

Select d.Division, b.BudgetID, o.Name as OrgName, o.OrganizationID,
b.Description as BudgetDescription, b.BudgetYear,ba.budgetID as budgetIDAccess
FROM budgets b
LEFT JOIN organization o ON o.OrganizationID = b.OrganizationID
LEFT JOIN division d ON d.DivisionID = o.DivisionID
JOIN budgets_access ba ON ba.budgetID = b.budgetID
WHERE b.BudgetYear = '2008'
AND ba.StaffID = '113'

Problem is this only displayes the budgets the staff member has access to. I need to display ALL budgets and the budgets the staff member has access to.

I changed the budgets_access join to left right and outer and could not get the desired results. Basically the budgetIDAccess field will have a value if the staff member has access or null if the staff member doesn't have access.

Any suggestions on accomplishing this with 1 select statement would be greatly appreciated.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:54:27

may be this

Select d.Division, b.BudgetID, o.Name as OrgName, o.OrganizationID, 
b.Description as BudgetDescription, b.BudgetYear,ba.budgetID as budgetIDAccess 
FROM budgets b
LEFT JOIN organization o ON o.OrganizationID = b.OrganizationID 
LEFT JOIN division d ON d.DivisionID = o.DivisionID 
LEFT JOIN budgets_access ba ON ba.budgetID = b.budgetID
AND b.BudgetYear = '2008'
AND ba.StaffID = '113'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:56:24

What is the structure of budgets_access table?

E 12°55'05.63"
N 56°04'39.26"

JimAmigo
Posting Yak Master

119 Posts

Posted - 2008-09-26 : 11:30:19

quote:

Originally posted by visakh16

may be this

Select d.Division, b.BudgetID, o.Name as OrgName, o.OrganizationID, 
b.Description as BudgetDescription, b.BudgetYear,ba.budgetID as budgetIDAccess 
FROM budgets b
LEFT JOIN organization o ON o.OrganizationID = b.OrganizationID 
LEFT JOIN division d ON d.DivisionID = o.DivisionID 
LEFT JOIN budgets_access ba ON ba.budgetID = b.budgetID
AND b.BudgetYear = '2008'
AND ba.StaffID = '113'

Tried this and didn't work. Just gave me the budgets the staff memeber has access to.

JimAmigo
Posting Yak Master

119 Posts

Posted - 2008-09-26 : 11:32:37

quote:
Originally posted by Peso

What is the structure of budgets_access table?

E 12°55'05.63"
N 56°04'39.26"

budget_access structure

Int, BudgetAccessID (PK, autonumber)
Int, BudgetID
Int, StaffID
Nvarchar, BudgetYear

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:39:34

quote:
Originally posted by JimAmigo

I need to display ALL budgets and the budgets the staff member has access to.

What do you mean with this sentence?
If you delete the

AND ba.StaffID = '113'

line, you will get ALL budgets.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 11:49:20

quote:

Originally posted by JimAmigo

quote:

Originally posted by visakh16

may be this

Select d.Division, b.BudgetID, o.Name as OrgName, o.OrganizationID, 
b.Description as BudgetDescription, b.BudgetYear,ba.budgetID as budgetIDAccess 
FROM budgets b
LEFT JOIN organization o ON o.OrganizationID = b.OrganizationID 
LEFT JOIN division d ON d.DivisionID = o.DivisionID 
LEFT JOIN budgets_access ba ON ba.budgetID = b.budgetID
AND b.BudgetYear = '2008'
AND ba.StaffID = '113'

Tried this and didn't work. Just gave me the budgets the staff memeber has access to.

nope it will return you all records in Budjet table with matching values only for those having records in budgets_access

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources