SQL Query to conditionally join columns from different databases

Question

I am migrating data from an old DB to a new one. My goal is to move a user's permissions from the old DB to the new DB. Both the old and new DBs already have matching user IDs.

The problem is that the old database has multiple rows for each user's permissions( Ex. User #3 might have 5 rows with different sets of permissions based on the organization id in the table's second column.

How can I:

Populate the new table with the user's highest permission only for each ('Admin' being the highest, 'Read' permission being the lowest authority)

OLD_DB USER TABLE:

ID     ORG     ADMIN   EDIT    READ
1       43       0       1      0
1       200      1       0      0
1       76       0       0      1
2       43       0       1      0
2       76       0       0      1

NEW_DB USER TABLE:

ID     LEVEL
1        0
2        0
3        0
4        0
5        0

NEW_DB USER TABLE GOAL:

ID     LEVEL
1      ADMIN
2      EDIT
3      READ
4      READ
5      EDIT

I have no interest in the user 'ORG' relation anymore and just want their highest permission level.

I am using Sequel Pro, which may offer a solution in it's GUI, but i'm not aware of it. My latest attempt in code to just obtain the admin users (with no luck) was:

SELECT admin FROM old_db.user oldpermission
JOIN scope FROM homestead_test.users newpermission
ON oldpermission.ID = newpermission.ID;

This is a bit trickier because the values you need could be in one of three different columns. I would probably do three different queries. First, change everything to READ. Then check to see if edit is true, if so change, those rows to EDIT. then check if admin is true. If so, change those rows to ADMIN — cullanrocks, Sep 03 '19 at 20:04

score 2 · Answer 1 · answered Sep 03 '19 at 20:12

 SELECT ID, CASE WHEN MAX(ADMIN) = 1 THEN 'ADMIN'
                 WHEN MAX(EDIT)  = 1 THEN 'EDIT'
                 WHEN MAX(READ)  = 1 THEN 'READ'
            END as LEVEL
 FROM OLD_DB 
 GROUP BY ID

You probably can simplify the last one to

 ELSE 'READ'

Because, if fail first 2 then only READ permission is available

SQL Query to conditionally join columns from different databases

1 Answers1