having trouble with my join statement

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.
LVL 34
Big MontyWeb Ninja at largeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Your select limits the response columns to acc.* account tanks only.

It is best to explicitly list the columns you are interested in.
Acc.column1,u.column3, etc.
Big MontyWeb Ninja at largeAuthor Commented:
i understand that, i haven't gone through and decided on each column that I need yet. Regardless of that, I get different totals of rows returned when I add in the last 2 join statements, when I expect to get the same.

for what it's worth, I've tried specifying columns from both tables, but the results are the same.
arnoldCommented:
You may need to use cte to overlay user with the results from these queries.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Scott PletcherSenior DBACommented:
Those rows must not have a matching row in the new tables.  Change the INNER joins to LEFT joins:

      left outer join UserAccountIDs ua on sa.accountID = ua.accountID
      left outer join Users u on u.userID = ua.userID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big MontyWeb Ninja at largeAuthor Commented:
can you provide an example?
lcohanDatabase AnalystCommented:
I believe less rows means not all siteAccess sa link to a Users u on u.userID via UserAccountIDs ua
so the disconnect is somewhere between
...
      inner join UserAccountIDs ua on sa.accountID = ua.accountID
      inner join Users u on u.userID = ua.userID
...

as far as duplicates see if a SELECT DISTINCT acc.*,   u.userID, u.MostRecentVisitDate
FROM Accounts acc.... helps


you can try this:




select distinct 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
      left join UserAccountIDs ua on sa.accountID = ua.accountID
      left join Users u on u.userID = ua.userID
where acc.adminID = 188 and
        sa.SiteID = 826906
order by AccountID
arnoldCommented:
;with cte
( your select query) as new_tablename
Select columns from new_tabname join users on new_tablename.userid=users.userid

Note you select query within cte need to be unique, or there might be referencing
I.e. Acc.userid ,...
I.e. Using explicit column references means you will not have duplicated information.
Big MontyWeb Ninja at largeAuthor Commented:
the LEFT OUTER JOIN  did the trick, my final query looks like:

select distinct (acc.AccountID), 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
	LEFT OUTER JOIN UserAccountIDs ua on sa.accountID = ua.accountID 
	LEFT OUTER JOIN Users u on u.userID = ua.userID
where acc.adminID = 188 and
	  sa.SiteID = 826906
order by AccountID

Open in new window


thx to both of you for the help!
arnoldCommented:
Check out the with cte option for future use.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.