If I've understood your latest description correctly, the query you want needs for each challenge two joins to the users table, something like this ...
select
c.challenger_user_id as challengeID,
uu.user_id,
concat( uu.first_name,' ', uu.last_name ) as 'user name',
uc.user_id as challenger_user_id,
concat( uc.first_name,' ', uc.last_name ) as 'challenger name'
from challengers c
join users uu on uu.user_id=c.user_id
join users uc on uc.user_id=c.challenger_id
order by 1;
+-------------+---------+-----------------+--------------------+-----------------+
| challengeID | user_id | user name | challenger_user_id | challenger name |
+-------------+---------+-----------------+--------------------+-----------------+
| 1 | 1103 | User2 Last2 | 1001 | Jassim Al Rahma |
| 2 | 1102 | User1 Last1 | 1001 | Jassim Al Rahma |
| 3 | 1001 | Jassim Al Rahma | 1100 | User3 Last3 |
| 4 | 1051 | User5 Last5 | 1082 | User4 Last4 |
| 5 | 1045 | User7 Last7 | 1049 | User6 Last6 |
| 6 | 1001 | Jassim Al Rahma | 1042 | User8 Last8 |
+-------------+---------+-----------------+--------------------+-----------------+
Is that correct? If so, some object names in this db are susceptible to being misunderstood. Table and column names are most useful when they're most accurately descriptive, so ...
- the challengers table actually holds info on challenges, not just challengers, so it should be named challenges
- for the same reason, its PK should be named challenge_id
- the name of the column identifying the challenger's user_id ought to be challenger_user_id.
If not, OTOH, we need another description of the model.
Edited 1 time(s). Last edit at 06/12/2022 07:09PM by Peter Brawley.