Avatar of Big Monty
Big Monty
Flag for United States of America asked on

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.
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
arnold

8/22/2022 - Mon
arnold

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 Monty

ASKER
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.
arnold

You may need to use cte to overlay user with the results from these queries.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Big Monty

ASKER
can you provide an example?
SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

;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 Monty

ASKER
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!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

Check out the with cte option for future use.