Return single row from SQL Query of many tables with common UserId key

Question

Is the a better way of returning a single row from a SQL Query that has multiple tables with a common UserId Key? I also referred to LINK but result was not what I'm looking for.

Also, they are many users which are assigned with many ModuleAccess, many PageAccess, many catalogAccess, Only 1 UserType, And Only 1 SystemAccess. User table has go many users.

I tried this But did no work:

SELECT        us.userId, us.username, us.email, us.isAdministrator, us.status, us.FullName, ut.userTypeId, ut.typeName, ut.levelName, sys.sysAccessId, sys.adminDashboard, sys.accessName, sys.standardDashboard, 
                         sys.marginChart, sys.expiringChart, sys.increasingChart, sys.viewCatalogue, sys.importList, sys.exportList, sys.masterDataMain, sys.changesNeed, md.moduleId, md.moduleName, md.moduleUrl, 
                         pg.pageId, pg.pageName, pg.pageUrl, pg.pagePermission, pg.pageAccess,cat.catAccId, cat.HasAccess
FROM            dbo.mp_Users AS us 
                         INNER JOIN dbo.mp_UserType AS ut ON us.userId = ut.userId 
                         INNER JOIN dbo.mp_PageAccess AS pg ON us.userId = pg.userId 
                         INNER JOIN dbo.mp_ModuleAccess AS md ON us.userId = md.userId 
                         INNER JOIN dbo.mp_SystemAccess AS sys ON us.userId = sys.userId 
                         INNER JOIN dbo.mp_CatalogAccess AS cat ON us.userId = cat.userId

What I want is something link this:

This is my current result query :

Any One has a better way of Query this above SQL? Thank you

_________________After few attempt_______________Query error

SELECT        
    dbo.mp_Users.userId,     dbo.mp_Users.username,     dbo.mp_Users.email,     dbo.mp_Users.isAdministrator,     dbo.mp_Users.status,     dbo.mp_Users.FullName,     dbo.mp_UserType.userTypeId, 
    dbo.mp_UserType.typeName,     dbo.mp_UserType.levelName,     dbo.mp_SystemAccess.sysAccessId,     dbo.mp_SystemAccess.adminDashboard,     dbo.mp_SystemAccess.accessName,     dbo.mp_SystemAccess.standardDashboard, 
    dbo.mp_SystemAccess.marginChart,     dbo.mp_SystemAccess.expiringChart,     dbo.mp_SystemAccess.increasingChart,     dbo.mp_SystemAccess.viewCatalogue,     dbo.mp_SystemAccess.importList, 
    dbo.mp_SystemAccess.exportList,     dbo.mp_SystemAccess.masterDataMain,     dbo.mp_SystemAccess.changesNeed,     
    sum(dbo.mp_ModuleAccess.moduleId), 
    max(dbo.mp_ModuleAccess.moduleName), 
    max(dbo.mp_ModuleAccess.moduleUrl), 
    sum(dbo.mp_PageAccess.pageId), 
    max(dbo.mp_PageAccess.pageName), 
    max(dbo.mp_PageAccess.pageUrl), 
    max(dbo.mp_PageAccess.pagePermission), 
    max(dbo.mp_PageAccess.pageAccess),
    sum(dbo.mp_CatalogAccess.catAccId), 
    max(dbo.mp_CatalogAccess.HasAccess)
FROM dbo.mp_Users 
 INNER JOIN dbo.mp_UserType  ON dbo.mp_Users.userId = dbo.mp_UserType.userId 
 INNER JOIN dbo.mp_PageAccess ON dbo.mp_Users.userId = dbo.mp_PageAccess.userId 
 INNER JOIN dbo.mp_ModuleAccess ON dbo.mp_Users.userId = dbo.mp_ModuleAccess.userId 
 INNER JOIN dbo.mp_SystemAccess ON dbo.mp_Users.userId = dbo.mp_SystemAccess.userId 
 INNER JOIN dbo.mp_CatalogAccess ON dbo.mp_Users.userId = dbo.mp_CatalogAccess.userId
GROUP BY dbo.mp_Users.userId

In my opinion, this query is as good as it gets, in syntax readability and performance. Do you have any specific ideas on what do you mean by "better" ? — George Menoutis, Jun 01 '18 at 09:28
@GeorgeMenoutis, Thanks for your reply, By Better I mean that I want something to fulfill my goal. I have a web application page which need to display list of users with their access information. the user has to show only once not twice. If Possible to Group the Module Access, PageAccess and CatalogAccess which does add more columns in a Query. do you have an answer to that? — PatsonLeaner, Jun 01 '18 at 09:34
Can you show the result of your current query or the data present in your tables? — Be1ng_Kr1Sh, Jun 01 '18 at 09:35
@Be1ng_Kr1Sh, My current query result I just added on my questions. Please have a looks — PatsonLeaner, Jun 01 '18 at 09:39
So you need to use a group by userID clause. Tell us: in case of multiple Modula Accesses, PAge Accesses and of other fields with multiple values per userID, what aggregation should we do? Keep the maximum?minimum? Maybe it's guranteed they will be the same so it doesn't matte? — George Menoutis, Jun 01 '18 at 09:44
@GeorgeMenoutis, That is what I think may work. But do you have an answer on that? like tried to answer to my question so that I may try it — PatsonLeaner, Jun 01 '18 at 09:46
In your new screenshot, we cannot see the usernames. Are they different? If they are, which one do you need to keep? — George Menoutis, Jun 01 '18 at 09:49
@GeorgeMenoutis, ok, I Will upload a new List with username clearly showing. Thanks — PatsonLeaner, Jun 01 '18 at 09:57
@DhruvJoshi I tried the Distinct but did not work because it's the userID which is found in all other tables. so, maybe grouping or MAX() function will work but not sure how! — PatsonLeaner, Jun 01 '18 at 10:16
Nice of you to show the usernames and the email, but you can get in serious trouble with GDPR - please hide them again!!! — George Menoutis, Jun 01 '18 at 10:16
Usernames and email still accessible in edit history. Flagged for moderator attention. As hinted at by @GeorgeMenoutis, you are still in violation of GDPR (if applicable). If GDPR applies (and chances are it does), you need to report this to the proper authorities (*even if a moderator chooses to delete the entire question!*). — HoneyBadger, Jun 01 '18 at 10:58
@HoneyBadger, Please tell me how to clean the history edit of my question? I want this question answer and no GDPR follow it behind. pls help. thanks — PatsonLeaner, Jun 01 '18 at 11:10
Only a moderator can clear the history (if it's possible at all). Nevertheless, the damage is done, and it should be reported. You should talk to your security officer. — HoneyBadger, Jun 01 '18 at 11:11
@HoneyBadger, All the Data used in this question was just dump and junk data. There is no personal information shared in this topic. Email, name, username, all that are just samples and does not exist. Hope this clear all. — PatsonLeaner, Jun 01 '18 at 11:26
It does clear it up, you could have made that clearer before though. — HoneyBadger, Jun 01 '18 at 11:27

George Menoutis · Accepted Answer · 2018-06-01T10:27:09.457

2

You can add

group by userId

in the end of your code, and then, at the select section,for all fields except userID, you will have to use

max(fieldname)

instead of fieldname eg

max(us.username), max(us.email)

Be careful, though. I proposed max() because I see that for every userID, the multiple values of your fields have the same value - however, you must be sure this is the case. If there is some field for which there are multiple values, you will have to identify in what way we should select one of these to present in the SELECT section.

Here are al the fields with max(), use with caution:

SELECT        
    us.userId, 
    max(us.username), 
    max(us.email), 
    max(us.isAdministrator), 
    max(us.status), 
    max(us.FullName), 
    max(ut.userTypeId), 
    max(ut.typeName), 
    max(ut.levelName), 
    max(sys.sysAccessId), 
    max(sys.adminDashboard), 
    max(sys.accessName), 
    max(sys.standardDashboard), 
    max(ys.marginChart), 
    max(sys.expiringChart), 
    max(sys.increasingChart), 
    max(sys.viewCatalogue), 
    max(sys.importList), 
    max(sys.exportList), 
    max(sys.masterDataMain), 
    max(sys.changesNeed), 
    max(md.moduleId), 
    max(md.moduleName), 
    max(md.moduleUrl), 
    max(pg.pageId), 
    max(pg.pageName), 
    max(pg.pageUrl), 
    max(pg.pagePermission), 
    max(pg.pageAccess),
    max(cat.catAccId), 
    max(cat.HasAccess)
FROM            dbo.mp_Users AS us 
 INNER JOIN dbo.mp_UserType AS ut ON us.userId = ut.userId 
 INNER JOIN dbo.mp_PageAccess AS pg ON us.userId = pg.userId 
 INNER JOIN dbo.mp_ModuleAccess AS md ON us.userId = md.userId 
 INNER JOIN dbo.mp_SystemAccess AS sys ON us.userId = sys.userId 
 INNER JOIN dbo.mp_CatalogAccess AS cat ON us.userId = cat.userId
GROUP BY us.userId

edited Jun 01 '18 at 10:27

answered Jun 01 '18 at 10:15

George Menoutis

6,894
3
19
43

I tried `(select userId, moduleId,moduleUrl, MAX(moduleName) as ModuleName from dbo.mp_ModuleAccess group by userId) md ON ut.userId = md.userId` but still casting an error. Please use my SQL Query and come up with a MAX() approach of the fielName. Thanks – PatsonLeaner Jun 01 '18 at 10:22
check it out now (the funksoul brother) – George Menoutis Jun 01 '18 at 10:25
This was my first attempt for max. Remember, ModuleAccess, PageAccess, CatalogAccess Tables are these that add more (same user) view repeatly` But I want all together in one place. like only one Line for user1, one line for user2, and so on. – PatsonLeaner Jun 01 '18 at 10:27
sorry, i forgot the group by at the end - try now – George Menoutis Jun 01 '18 at 10:27
show an Error: `Column 'dbo.mp_Users.isAdministrator' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` why is that? – PatsonLeaner Jun 01 '18 at 10:31
1

The select might only have fields that are in the group by clause, else they must have an aggregation function (eg max) on them. You must use max(dbo.mp_Users.isAdministrator) , as for every other field. – George Menoutis Jun 01 '18 at 10:33
Still not working, Same error, I did what you ask but with no help. I think the Group and the max() can work to gather but in my case seems not the case. – PatsonLeaner Jun 01 '18 at 10:47
1

This error and what I am telling you is easy and definite. Post your entire query in the end of your original question. – George Menoutis Jun 01 '18 at 10:51
Let me help you to understand what I clearly want. I want the List to show user1, has total(Module), has total(pages), has total(catalog) assigned to him in a single row, then follow by user2, user3, and so on. – PatsonLeaner Jun 01 '18 at 11:06
ok, then for Module,pages,and catalog, instead of max(), use sum() – George Menoutis Jun 01 '18 at 11:11
@GeorgeMenoutis (and the error) were very clear: You need an aggregate function on **every** column not in the `GROUP BY` – HoneyBadger Jun 01 '18 at 11:23
@HoneyBadger, the error says: `Column 'dbo.mp_Users.username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause` so, there is no way around it. I tried to use MAX in every columns but was not successful. – PatsonLeaner Jun 01 '18 at 11:28
@PatsonLeaner You get that error on the query you posted last in your question? – HoneyBadger Jun 01 '18 at 11:30
@HoneyBadger Please check the recent update. It's come from there! – PatsonLeaner Jun 01 '18 at 11:33
@PatsonLeaner, why did you not put an aggregate function on all attributes not in the `group by`, as you've been advised to? – HoneyBadger Jun 01 '18 at 11:35
@HoneyBadger, I need an illustration to understand. I'm I did all of that but without success. Post an answer, perhaps it's maybe or may lead to the answer I'm looking for. Thanks – PatsonLeaner Jun 01 '18 at 11:37
@PatsonLeaner, I can't give a better answer then this answer. Use `max()`, `sum()` or any other aggregate function on *every* column except `us.userId`, since that is in the `group by`. – HoneyBadger Jun 01 '18 at 11:41

score 0 · Answer 2 · answered Jun 01 '18 at 09:49

0

I think you can :
1 use LIMIT 1 when just one row of data
2 use a fairly typed column in the Join table and index it

answered Jun 01 '18 at 09:49

junmin dai

19
2

`LIMIT 1` won't work in sql server. What do you mean with point 2? – HoneyBadger Jun 01 '18 at 10:09
@HoneyBadger Limit Will Work, But Not according to what I'm looking for. I first approach will be MAX() function and Grouping where the user has multiple entries – PatsonLeaner Jun 01 '18 at 10:14
@PatsonLeaner No, `limit` doesn't exist in sql server, it's a mysql/Oracle (perhaps others as well) thing. – HoneyBadger Jun 01 '18 at 10:48

Return single row from SQL Query of many tables with common UserId key

2 Answers2