I'm trying to wrap my head around views. I think they are what will work best for what I need. I'll create three example tables here, but it should illustrate what I'm trying to do.
First, a table that holds the list of all Discord users who've used, or been searched by, my product:
mysql> DESCRIBE discord_users;
+----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| no | int | NO | PRI | NULL | auto_increment |
| discord_user | varchar(19) | NO | UNI | NULL | |
| is_banned | tinyint | NO | | 0 | |
| is_whitelisted | varchar(45) | NO | | 0 | |
| note | varchar(256) | YES | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------+--------------+------+-----+-------------------+-----------------------------------------------+
mysql> SELECT * FROM discord_users;
+----+--------------------+-----------+----------------+---------------+---------------------+
| no | discord_user | is_banned | is_whitelisted | note | timestamp |
+----+--------------------+-----------+----------------+---------------+---------------------+
| 1 | 228937760390643713 | 1 | 0 | Stev#6666 | 2023-04-14 06:14:39 |
| 2 | 253725613439975426 | 0 | 1 | Harsh#3028 | 2023-04-14 06:14:39 |
| 3 | 533022512372645916 | 0 | 1 | ItsBranK#1337 | 2023-04-14 06:14:39 |
+----+--------------------+-----------+----------------+---------------+---------------------+
Second, a table that holds a list of all of the Epic Games users who've used, or been searched by, my product:
mysql> DESCRIBE epic_games_users;
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| no | int | NO | PRI | NULL | auto_increment |
| epic_games_user | varchar(32) | NO | UNI | NULL | |
| is_banned | tinyint | NO | | 0 | |
| is_whitelisted | tinyint | NO | | 0 | |
| note | varchar(256) | YES | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
mysql> SELECT * FROM epic_games_users;
+----+----------------------------------+-----------+----------------+-----------+---------------------+
| no | epic_games_user | is_banned | is_whitelisted | note | timestamp |
+----+----------------------------------+-----------+----------------+-----------+---------------------+
| 1 | d8603dd248ad45bcb47305a68841d676 | 0 | 0 | RL.Plus | 2023-04-14 06:15:25 |
| 2 | 0274508828c347ddaff7bb9051d2303d | 0 | 1 | EquinoxRL | 2023-04-14 06:15:25 |
+----+----------------------------------+-----------+----------------+-----------+---------------------+
Last, I have a table of Discord and Epic Games users who've linked their accounts. Foreign keys are discord_user and epic_games_user to the tables above.
mysql> DESCRIBE links;
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| no | int | NO | PRI | NULL | auto_increment |
| discord_user | varchar(19) | NO | UNI | NULL | |
| epic_games_user | varchar(32) | NO | UNI | NULL | |
| note | varchar(256) | NO | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
mysql> SELECT * FROM links;
+----+--------------------+----------------------------------+------+---------------------+
| no | discord_user | epic_games_user | note | timestamp |
+----+--------------------+----------------------------------+------+---------------------+
| 1 | 228937760390643713 | d8603dd248ad45bcb47305a68841d676 | NULL | 2023-04-14 06:16:07 |
+----+--------------------+----------------------------------+------+---------------------+
Note: Not every user in the discord_users and epic_games_users tables will appear in links.
I would like to create a view that resembles the following - the best way I know how to describe it:
+--------------------+----------------------------------+-----------+----------------+
| discord_user | epic_games_user | is_banned | is_whitelisted |
+--------------------+-------------+--------------------+-----------+----------------+
| 228937760390643713 | d8603dd248ad45bcb47305a68841d676 | 1 | 0 |
| 253725613439975426 | NULL | 0 | 1 |
| 533022512372645916 | NULL | 0 | 1 |
| NULL | 0274508828c347ddaff7bb9051d2303d | 0 | 1 |
+--------------------+----------------------------------+-----------+----------------+
It should list all discord_user and epic_games_user, next to the linked account, if they have one. If it does not have a linked account, the other user column, be it discord_user or epic_games_user, should be null.
is_banned and is_whitelisted should be true if those columns are true in the discord_users or epic_games_users tables. Obviously if they do not appear in links, it would only be dependant on the table it exists.
I honestly have no idea where to start on this and I would appreciate any help. I believe I can easily list those who appear in links, and whether they are banned or whitelisted. The trickly part is including discord_user and epic_games_user that do not appear in links, and prevent duplicates.
Thanks for you help!
The first query gets all the users in discord_users and, if one exists, the associated user in epic_games_users. The second query returns the users which exist only in epic_games_users.
SELECT
du.discord_user,
egu.epic_games_user,
(du.is_banned OR IFNULL(egu.is_banned, 0)) AS is_banned,
(du.is_whitelisted OR IFNULL(egu.is_whitelisted, 0)) AS is_whitelisted
FROM discord_users du
LEFT JOIN links l ON du.discord_user = l.discord_user
LEFT JOIN epic_games_users egu ON l.epic_games_user = egu.epic_games_user
UNION ALL
SELECT
NULL,
egu.epic_games_user,
egu.is_banned,
egu.is_whitelisted
FROM epic_games_users egu
LEFT JOIN links l ON egu.epic_games_user = l.epic_games_user
WHERE l.epic_games_user IS NULL
outputs:
| discord_user | epic_games_user | is_banned | is_whitelisted |
|---|---|---|---|
| 228937760390643713 | d8603dd248ad45bcb47305a68841d676 | 1 | 0 |
| 253725613439975426 | NULL | 0 | 1 |
| 533022512372645916 | NULL | 0 | 1 |
| NULL | 0274508828c347ddaff7bb9051d2303d | 0 | 1 |
and then just precede the above query with CREATE VIEW:
CREATE VIEW `your_view_name` AS
...