MySQL Forums
Forum List  »  Newbie

Re: Query challengers table
Posted by: Peter Brawley
Date: June 12, 2022 07:08PM

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.

Options: ReplyQuote


Subject
Written By
Posted
June 10, 2022 01:56PM
Re: Query challengers table
June 12, 2022 07:08PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.