SQL query to find unique occurrences inside cross join

I have three tables: Users, Rights and UserRights, which is mapping values from two first tables. Example:

Table 'Users'

Table 'Rights'

Table 'UserRights'

I have a query to check whether the user has the appropriate right. The output of the query looks like this:

The problem is that inside the table 'Rights' there could be some duplicates with other relationships. Because of them script output can look like this:

I'd like to select only distinct rights and I want to select 'true' value for each such right if at least one of duplicates has 'true'.

I use this query to achieve the expecting result but it does not consider if any of duplicates has 'true' value for the right:

SELECT u.login,
       LISTAGG(DISTINCT r.name || '=' || 
               CASE
                 WHEN ur.user_id IS NOT NULL THEN
                  'true'
                 ELSE
                  'false'
               END,
               '; ') 
       WITHIN GROUP ( ORDER BY r.name ) AS rights
  FROM users u
 CROSS JOIN rights r
  LEFT JOIN userrights ur
    ON ur.user_id = u.id
   AND ur.right_id = r.id
 GROUP BY u.login;

Also, I'd like to get rid of printing r.name, but I use it to filter out unique values.

We are migrating from this schema now, so it is more difficult to correct DB consistency than correct the script.

Fix your data (not your query) by adding primary key/unique constraints to your tables:

CREATE TABLE Users (
  ID    NUMBER(8,0) PRIMARY KEY,
  LOGIN VARCHAR2(20) NOT NULL UNIQUE
);

CREATE TABLE Rights (
  ID   NUMBER(5,0) PRIMARY KEY,
  NAME VARCHAR2(20) UNIQUE NOT NULL
);

CREATE TABLE UserRights (
  USER_ID REFERENCES Users(id),
  RIGHT_ID REFERENCES Rights(id),
  PRIMARY KEY (user_id, right_id)
);

INSERT INTO users (id, login)
SELECT 1, 'test1' FROM DUAL UNION ALL
SELECT 2, 'test2' FROM DUAL;

INSERT INTO rights (id, name)
SELECT 1, 'read' FROM DUAL UNION ALL
SELECT 2, 'write' FROM DUAL;

INSERT INTO UserRights (USER_ID, RIGHT_ID)
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL;

Then it is impossible to have duplicate rights in a table.

It is also impossible (with or without the constraints) to have a right be simultaneously true and false as true is given by the existence of a matching row in the UserRights table and false is given by the non-existence of a matching row in the UserRights table and there can never be a case when for the same user and right combination there both exists and does not exist a row in the UserRights table; the behaviours are mutually exclusive.

If, before you apply the constraints, you have duplicates then you can get rid of them using:

DELETE FROM userrights
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY user_id, right_id ORDER BY ROWID) AS rn
    FROM   userrights
  )
  WHERE rn > 1
);

After removing duplicates and applying your constraints, then you can use your query (or aggregating on the primary key):

SELECT MAX(u.login) AS login,
       LISTAGG(r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN userrights ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id

Which, for your sample data, outputs:

fiddle

If you do have duplicates in the UserRights table then you should fix the data and add constraints; if you are not going to do that (you should) then you can use DISTINCT:

SELECT MAX(u.login) AS login,
       LISTAGG(r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN (SELECT DISTINCT * FROM userrights) ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id;

or:

SELECT MAX(u.login) AS login,
       LISTAGG(DISTINCT r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN userrights ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id;

fiddle

Update: Rights with duplicate names

If you have rights with duplicate names then fix your data.

For example, given the data:

CREATE TABLE Users (
  ID    NUMBER(8,0) PRIMARY KEY,
  LOGIN VARCHAR2(20) NOT NULL UNIQUE
);

CREATE TABLE Rights (
  ID   NUMBER(5,0) PRIMARY KEY,
  NAME VARCHAR2(20) NOT NULL
);

CREATE TABLE UserRights (
  USER_ID REFERENCES Users(id),
  RIGHT_ID REFERENCES Rights(id),
  CONSTRAINT userrights__uid__rid__pk PRIMARY KEY (user_id, right_id)
);

INSERT INTO users (id, login)
SELECT 1, 'test1' FROM DUAL UNION ALL
SELECT 2, 'test2' FROM DUAL UNION ALL
SELECT 3, 'test3' FROM DUAL UNION ALL
SELECT 4, 'test4' FROM DUAL;

INSERT INTO rights (id, name)
SELECT 1, 'read' FROM DUAL UNION ALL
SELECT 2, 'write' FROM DUAL UNION ALL
SELECT 3, 'write' FROM DUAL;

INSERT INTO UserRights (USER_ID, RIGHT_ID)
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 3 FROM DUAL;

Then you can merge the duplicates using:

ALTER TABLE userrights MODIFY CONSTRAINT userrights__uid__rid__pk DISABLE;

UPDATE userrights ur
SET right_id = (
  SELECT new_id
  FROM   (
    SELECT id,
           MIN(id) OVER (PARTITION BY name) AS new_id
    FROM   rights
  ) r
  WHERE  r.id = ur.right_id
);

DELETE FROM userrights
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY user_id, right_id ORDER BY ROWID) AS rn
    FROM   userrights
  )
  WHERE  rn > 1
)

ALTER TABLE userrights MODIFY CONSTRAINT userrights__uid__rid__pk ENABLE;

And delete (now unused) the duplicate rights:

DELETE FROM rights
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
    FROM   rights
  )
  WHERE  rn > 1
)

Then you can add a constraint to prevent this happening again:

ALTER TABLE rights ADD CONSTRAINT rights__name__u UNIQUE (name);

Then:

SELECT MAX(u.login) AS login,
       LISTAGG(r.name || '=' || NVL2(ur.user_id, 'true', 'false'), ';')
         WITHIN GROUP (ORDER BY r.name) AS rights
FROM   users u
       CROSS JOIN rights r
       LEFT OUTER JOIN userrights ur
       ON (u.id = ur.user_id AND r.id = ur.right_id)
GROUP BY u.id

Outputs:

fiddle

An option to do it could be like here:

SELECT  LOGIN, 
        CASE WHEN READ_RIGHT Is Null THEN 'Read=false;' ELSE 'Read=true;' END || 
        CASE WHEN WRITE_RIGHT Is Null THEN 'Write=false;' ELSE 'Write=true;' END "RIGHTS"
FROM
    (   SELECT  u.LOGIN, ROW_NUMBER() OVER(Partition By u.ID Order  By u.ID, Nvl(r.ID, 0)) "RN",
                CASE  WHEN Upper(r.A_NAME) = 'READ' THEN r.A_NAME 
                ELSE  FIRST_VALUE(CASE  WHEN Upper(r.A_NAME) = 'READ' THEN r.A_NAME END) 
                                  OVER(Partition By u.ID Order  By u.ID, Nvl(r.ID, 0) Rows Between 1 Following And Unbounded Following) END "READ_RIGHT" ,
                --
                CASE  WHEN Upper(r.A_NAME) = 'WRITE' THEN r.A_NAME 
                ELSE FIRST_VALUE(CASE   WHEN Upper(r.A_NAME) = 'WRITE' THEN r.A_NAME END) 
                                 OVER(Partition By u.ID Order  By u.ID, Nvl(r.ID, 0) Rows Between 1 Following And Unbounded Following) END "WRITE_RIGHT"
        FROM    users u
        LEFT JOIN users_rights ur ON(u.ID = ur.USER_ID)
        LEFT JOIN rights r ON(r.ID = ur.RIGHT_ID)
        ORDER BY  u.ID, r.ID  
    )
WHERE   RN = 1

The subquery checks if any particular user has read, write or both rights placing them in the user's 1st row. Main query just concatinates values giving them true or false if they exist or not.
With your sample data:

WITH
	users AS
		(   Select 1 "ID",	'test1' "LOGIN" From Dual Union All
			Select 2,		'test2'  	    From Dual
		),
	rights AS
		(   Select 1 "ID",	'read'  "A_NAME"	From Dual Union All
			Select 2,		'write'  		    From Dual Union All
			Select 3,		'write'  		    From Dual
		),
	users_rights AS
		(   Select 1 "USER_ID",	1	"RIGHT_ID"	From Dual Union All
			Select 1,			2  		        From Dual Union All
			Select 1,			3  		        From Dual Union All
			Select 2,			2  		        From Dual
		)

... it would be:

R e s u l t :
LOGIN RIGHTS                
----- -----------------------
test1 Read=true;Write=true;   
test2 Read=false;Write=true;

Additionaly, if you have a user that is not present in users_rights table, let's say user ID=3 LOGIN=test3, then this will return another row like:

LOGIN RIGHTS                
----- -----------------------
test1 Read=true;Write=true;   
test2 Read=false;Write=true;  
test3 Read=false;Write=false;