I’d like some help with comparing the data from two databases about users and their permissions. There are three tables, one with the user information, a link table and one with the permissions.

I need to get the different users and permissions from both tables and compare the differences between the old and the new database. There are differences in permissions between the same users and differences between users which have been added or removed.

I have created a database link to enable querying the other database simultaneously, and currently I have a query like this:

SELECT
users1.userID,
users1.name,
users2.userID,
users2.name,
permissions1.permissionID,
permissions1.name,
permissions2.permissionID,
permissions2.name
FROM
users users1
FULL JOIN
users@dbLink users2 ON users1.name = users2.name
LEFT JOIN
userPermissions userPermissions1 ON users1.userID = userPermissions1.userID
LEF JOIN
Permissions permissions1 ON userPermissions1.permissionID = permissions1.permissionID
LEFT JOIN
userPermissions@dbLink userPermissions2 ON users2.userID = userPermissions2.userID
LEFT JOIN
permissions@dbLink permissions2 ON userPermissions2.permissionID = permissions2.permissionID;

However the output has a lot of replicated data with the same permission repeated for every different permission of the other databases user. like this:

usrs1.id |usrs1.name |usrs2.id |usrs2.name |prmssns1.id |prmsns1.name |prmsns2.id |prmsns2.name
_______________________________________________________________________________________________
001      | user1     | 001     | user1     | 001        | permission1 | 001       | permission1
001      | user1     | 001     | user1     | 002        | permission2 | 001       | permission1
001      | user1     | 001     | user1     | 003        | permission3 | 001       | permission1
001      | user1     | 001     | user1     | 004        | permission4 | 001       | permission1
001      | user1     | 001     | user1     | 005        | permission5 | 001       | permission1
001      | user1     | 001     | user1     | 001        | permission1 | 002       | permission2
001      | user1     | 001     | user1     | 002        | permission2 | 002       | permission2
001      | user1     | 001     | user1     | 003        | permission3 | 002       | permission2
001      | user1     | 001     | user1     | 004        | permission4 | 002       | permission2
001      | user1     | 001     | user1     | 005        | permission5 | 002       | permission2

I would like help to group the same permissions together for each user which is the same and leave a null space for each permission or user which one database has that the other doesn’t. Like this:

usrs1.id |usrs1.name |usrs2.id |usrs2.name |prmssns1.id |prmsns1.name |prmsns2.id |prmsns2.name
_______________________________________________________________________________________________
001      | user1     | 001     | user1     | 001        | permission1 | 001       | permission1
001      | user1     | 001     | user1     | 002        | permission2 | 002       | permission2
001      | user1     | 001     | user1     | 003        | permission3 | null      | null
001      | user1     | 001     | user1     | null       | null        | 004       | permission4
null     | null      | 002     | user2     | null       | null        | 001       | permission1
null     | null      | 002     | user2     | null       | null        | 002       | permission2
003      | user3     | null    | null      | 001        | permission1 | null      | null
003      | user3     | null    | null      | 002        | permission2 | null      | null

I’ve tried a variety of different joins, subqueries and group by functions but have run out of ideas, I would appreciate any suggestions on how to output the data correctly.