MYSQL union All with condition if condition matched choose first else 2nd

Question

In my case it is permissions and roles so i combined result with union all I just want check condition if user permission value = 0 then pick this else other one i am trying like this

SELECT username, orgId, uid, pid, perm FROM (
    SELECT users.username, users.orgId, user_roles.uid, role_perms.pid, role_perms.value AS perm
                    FROM user_roles INNER JOIN role_perms
                    ON (user_roles.rid = role_perms.rid) INNER JOIN user_users ON(user_roles.uid = users.uid) WHERE role_perms.pid = 9 AND users.orgId = 2 AND users.username IS NOT NULL AND users.username != ''
                    UNION ALL 
    SELECT users.username, users.orgId, user_perms.uid, user_perms.pid, user_perms.value AS perm
                    FROM user_perms INNER JOIN users ON(user_perms.uid = users.uid) WHERE user_perms.pid = 9 AND user_users.orgId = 2 AND user_users.username is not null and user_users.username != '') AS combPerm;

It gives result as if permission is denied for one user in user_perm table but that user also have role that contain particular permission

username | orgId | uid | pid | perm
abc@a.com  2       11    9     0
abc@a.com  2       11    9     1
xyz@a.com  2       91    9     1
dvc@a.com  2       88    9     1

In result i want abc@a.com only one time if it has perm 0 from user_perms table and all other record same, desired result is as

 username | orgId | uid | pid | perm
abc@a.com  2       11    9     0
xyz@a.com  2       91    9     1
dvc@a.com  2       88    9     1

score 2 · Accepted Answer · answered Mar 15 '18 at 16:32

2

You can just take the min(perm) and add a group by to your query

SELECT username, orgId, uid, pid, min(perm) FROM (

 -----the rest of the query

) AS combPerm group by username, orgId, uid, pid;

answered Mar 15 '18 at 16:32

isaace

3,336
1
9
22

No This will group by results which i already tried this not full fill condition of if user_perms.value = 0 then priority should be to that else to role_perm – user3712320 Mar 15 '18 at 16:37
please provide more sample data and your desired results. – isaace Mar 15 '18 at 16:42
1

@user3712320: It is not clear why you are not satisfied with this answer. It seems to do exactly what you have asked for: perm 0 has precedence over perm 1 for username + orgId + uid + pid. So please clarify. – Thorsten Kettner Mar 15 '18 at 16:50
1

@ Thorsten Kettner i accepted this anwer but now question came in my mind still its not complete if role_perms has 0 but user_perms have 1 then it should not get result with 0 because priority should be based on user_perms table, – user3712320 Mar 15 '18 at 17:07
@user3712320: It is good you've accepted this answer. I've posted another answer that kind of builds up on this solution and should solve the query precedence you are after. – Thorsten Kettner Mar 15 '18 at 19:02

score 1 · Answer 2 · answered Mar 15 '18 at 19:00

You have accepted isaace's answer on how to give perm 0 precedence over perm 1. But now you say, you would rather give query 2 results precedence over query 1 results.

In standard SQL this would be easily done. You'd add a rank key to the queries (select 1/2 as rankkey, ...) and rank your results with ROW_NUMBER and keep the best match or you'd do this with FETCH WITH TIES.

MySQL does neither support window functions such as ROW_NUMBER nor a limit clause that allows for ties. Many such things are solved with variables in queries in MySQL. Here is another trick:

What you have is something like

select username, orgId, uid, pid, perm from table1
union all
select username, orgId, uid, pid, perm from table2;

and isaace has shown you how to get the minimum perm per username, orgId, uid, pid. You however want somthing close but different :-) Here you go:

select username, orgId, uid, pid, max(keyperm) % 10 as perm
from
(
  select username, orgId, uid, pid, perm + 10 as keyperm from table1
  union all
  select username, orgId, uid, pid, perm + 20 as keyperm from table2
)
group by username, orgId, uid, pid
order by username, orgId, uid, pid;

What is happpening here? You see I add 10 to the perm in the first query and 20 to the perm in the second query. MAX gives me the higher of the two, which is the second if both are present. We end up with a perm that is either 10 or 11 or 20 or 21. The operation % 10 (modulo ten) removes the ten's place and only keeps the one's place, which is 0 or 1, the perm.

(If perms can be two digits, add 100 and 200 and use %100, if more digits ... well you got the idea.)

score 0 · Answer 3 · answered Mar 15 '18 at 16:28

0

SELECT * FROM (YOUR QUERY) t WHERE perm=0 GROUP BY username, orgId, uid,pid;

answered Mar 15 '18 at 16:28

ecp

319
1
6
18

This not gonna work if its so simple then whats need of UNION or UNION ALL – user3712320 Mar 15 '18 at 16:32

MYSQL union All with condition if condition matched choose first else 2nd

3 Answers3