Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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:
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:
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.
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
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
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
It is best to explicitly list the columns you are interested in.
Acc.column1,u.column3, etc.
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.
for what it's worth, I've tried specifying columns from both tables, but the results are the same.
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
left outer join UserAccountIDs ua on sa.accountID = ua.accountID
left outer join Users u on u.userID = ua.userID
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
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
;with cte
( your select query) as new_tablename
Select columns from new_tabname join users on new_tablename.userid=users
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.
( your select query) as new_tablename
Select columns from new_tabname join users on new_tablename.userid=users
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.
the LEFT OUTER JOIN did the trick, my final query looks like:
thx to both of you for the help!
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
thx to both of you for the help!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial