Replace the id's with name using single query

Question

SELECT team_with.participant1,team_with.participant2,team_with.participant3 
 FROM event,team_with 
 WHERE team_with.for_event_no=event.event_no AND 
 event.event_no=4 AND 
 team_with.participant1=9 OR 
 team_with.participant2=9 OR 
 team_with.participant3=9;

I have written the particular query, and obtained the required id's in a row. I am not able to modify this query such that, in place of these id's, names connected to the id's are displayed.

The student_detatil table consists of PK(sam_id) and the attribute name.

IDs displayed by the present query are FKs connected to student_detail.sam_id..

@Strawberry That would be a better solution if possible yes. — jpw, Feb 28 '16 at 00:09
Does this answer your question? [MySQL JOIN to replace IDs with value from another table](https://stackoverflow.com/questions/12431636/mysql-join-to-replace-ids-with-value-from-another-table) — mickmackusa, Oct 04 '21 at 22:02

score 0 · Accepted Answer · answered Feb 27 '16 at 23:22

0

It seems like a bad design to multiply columns storing different participants. Consider creating a separate row for each participant and storing them in a table. Your joining logic would also be easier.

Also, please use explicit JOIN syntax - it makes the query clearer and easier to understand by separating join logic with conditions for data retrieval.

Remember that operator AND has a precedence over OR, so that your event.event_no = 4 does not apply to each participant condition. I believe this was a mistake, but you are the one to judge.

As to the query itself, you could apply OR conditions into join, or simply join the student_detail table thrice.

SELECT 
    s1.name,
    s2.name,
    s3.name
FROM 
    event e 
    INNER JOIN team_with t ON t.for_event_no = e.event_no
    LEFT JOIN student_detail s1 ON s1.sam_id = t.participant1
    LEFT JOIN student_detail s2 ON s2.sam_id = t.participant2
    LEFT JOIN student_detail s3 ON s3.sam_id = t.participant3
WHERE
    e.event_no = 4
    AND ( t.participant1=9 OR t.participant2=9 OR t.participant3=9 );

answered Feb 27 '16 at 23:22

Kamil Gosciminski

16,547
8
49
72

Thank you. This works. Regarding the design, each event has different number of participants, it is not always 3. Therefore i had different columns stored for team members. Can you suggest something better? – klbm9999 Feb 27 '16 at 23:31
You should have a table for participants in an event and store the column as a Foreign Key to point to your table. This way a various number of participants wouldn't multiply your columns with an event – Kamil Gosciminski Feb 27 '16 at 23:34
But in that case, the number of tables would equal to no of events. Is it a good design to have so many number of tables? (i have 32 diferent events) – klbm9999 Feb 27 '16 at 23:37
It wouldn't. One table for events, one for details about participants and one for participants in an event. I don't know your schema, but that seems to be a topic for a different question. You can post it here asking for some help on the design too. – Kamil Gosciminski Feb 27 '16 at 23:38
My present schema has one table about student details, one for "participates in", one for event detail (as you suggested above). Now regarding the team for a particular participant in a particular event, i didnt want to create 30 different tables. Therefore i came up with one team_with table (max team size is 8 so 8 participant columns), one team_id, and one FK for_event_no to relate that team to a particular event. – klbm9999 Feb 27 '16 at 23:43
This my friend is a topic for a different question. Post it and I may take a look at it. We don't solve complex problems in one question because that is an off topic. – Kamil Gosciminski Feb 27 '16 at 23:44
Sure. Thanks for the help though – klbm9999 Feb 27 '16 at 23:45

Replace the id's with name using single query

1 Answers1