CODEWITHSUNDEEP
mysql
sebastian nielsen
sebastian nielsen

Reputation: 505

Combine multiple records in SQL with logical OR over every boolean

I have the following table, which describes permissions a specific user has on specific objects:

+------+-----------+----------+-----------+------------+------------+
| type | object_id | can_read | can_write | can_delete | can_create |
+------+-----------+----------+-----------+------------+------------+
|    0 |         1 |        1 |         0 |          0 |          1 |
|    0 |         1 |        1 |         1 |          0 |          0 |
|    0 |         2 |        1 |         0 |          0 |          0 |
|    1 |         1 |        1 |         1 |          1 |          0 |
+------+-----------+----------+-----------+------------+------------+

type and object_id is integers.

type is the object type. There exist different types of objects sharing the same object_id, but should be treated as different objects due to different "type".

can_read, can_write, can_delete, can_create is booleans, which describe what the current selected user can do on the (type, object_id) tuple.

This table is the output of a couple of UNION's, which both fetches rights that an individual user has on object_id, and rights that an specific "Group" as on object_id, where the individual user is member, which is then repeated for every object type.

Now to the problem. I now want to return rows that are distinct over type and object_id. For any duplicate records, the boolean values should be an logical "OR" over every row.

In this case, the above example table should be returned as:

+------+-----------+----------+-----------+------------+------------+
| type | object_id | can_read | can_write | can_delete | can_create |
+------+-----------+----------+-----------+------------+------------+
|    0 |         1 |        1 |         1 |          0 |          1 |
|    0 |         2 |        1 |         0 |          0 |          0 |
|    1 |         1 |        1 |         1 |          1 |          0 |
+------+-----------+----------+-----------+------------+------------+

In this case, since the selected user, has rights can_read=true, can_write=false, can_delete=false, can_create=true coming from a individual user right, on type=0,object_id=1. And then the same user has rights can_read=true, can_write=true, can_delete=false, can_create=false coming from a Group membership on type=0,object_id=1.

The effective rights is thus can_read=true, can_write=true, can_delete=false, can_create=true.

Upvotes: 0

Views: 778

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

could be you need an aggreagtion function for merge the values in this case max valeue for each type object_id

   select type, object_id
      , max(can_read) can_read
      , max(can_write) can_write
      , max(can_delete) can_delete
      , max(can_create) can_create
   from my_table 
   group by type, object_id

and for an union 

   select type, object_id
      , max(can_read) can_read
      , max(can_write) can_write
      , max(can_delete) can_delete
      , max(can_create) can_create
   from (

    select type, object_id, can_read, can_write, can_delete, can_create
    from table1
    union 

    select type, object_id, can_read, can_write, can_delete, can_create
    from table2
   ) t
   group by type, object_id

Upvotes: 1

Daniel E.
Daniel E.

Reputation: 2480

To get 1 if 1 line is 1 and 0 if both, you need to use MAX() function :

SELECT type, object_id,
 MAX(can_read),
 MAX(can_write),
 MAX(can_delete),
 MAX(can_create)
FROM myTable
GROUP BY type, object_id

Upvotes: 0

Related Questions