We help IT Professionals succeed at work.
Get Started

having trouble with my join statement

88 Views
Last Modified: 2015-10-09
I'm working on a query that'll pull certain users, depending on certain criteria (such as what group, zone, etc they belong to). An user belongs to an ACCOUNT, which in turn belongs to other types of privileges. When I run the following query, I get the results I expect:

select acc.*   
FROM Accounts acc
	INNER JOIN PrivDefs pd ON acc.Privs = pd.Priv 
	LEFT OUTER JOIN Sites s ON acc.DefaultSiteID = s.SiteID AND acc.AdminID = s.AdminID
	LEFT OUTER JOIN Groups g ON acc.DefaultGroupID = g.GroupID and acc.AdminID = g.AdminID
	LEFT OUTER JOIN AccountTemplateAddenda t ON acc.AccountID = t.AccountID
	INNER JOIN siteAccess sa ON acc.AccountID = sa.AccountID
where acc.adminID = 188 and
	  sa.SiteID = 826906
order by AccountID

Open in new window


however, I want to grab some columns out of the USERS table. I have a cross reference table called UserAccountIDs that has an accountID and userID columns. So I figured a simple join on those two tables would get me what I want:

select acc.*,   u.userID, u.MostRecentVisitDate
FROM Accounts acc
	INNER JOIN PrivDefs pd ON acc.Privs = pd.Priv 
	LEFT OUTER JOIN Sites s ON acc.DefaultSiteID = s.SiteID AND acc.AdminID = s.AdminID
	LEFT OUTER JOIN Groups g ON acc.DefaultGroupID = g.GroupID and acc.AdminID = g.AdminID
	LEFT OUTER JOIN AccountTemplateAddenda t ON acc.AccountID = t.AccountID
	INNER JOIN siteAccess sa ON acc.AccountID = sa.AccountID
	inner join UserAccountIDs ua on sa.accountID = ua.accountID 
	inner join Users u on u.userID = ua.userID
where acc.adminID = 188 and
	  sa.SiteID = 826906
order by AccountID

Open in new window


however, I am only getting back about half of the records, with a lot of duplicates. This seems like it should be pretty straight-forward, but I can't seem to get the results I want.
Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 2 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE