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.AccountIDwhere acc.adminID = 188 and sa.SiteID = 826906order by AccountID
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.MostRecentVisitDateFROM 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.userIDwhere acc.adminID = 188 and sa.SiteID = 826906order by AccountID
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
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.
;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.MostRecentVisitDateFROM 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.userIDwhere acc.adminID = 188 and sa.SiteID = 826906order by AccountID
It is best to explicitly list the columns you are interested in.
Acc.column1,u.column3, etc.