CODEWITHSUNDEEP
mysql
SubZero
SubZero

Reputation: 87

MySQL- Selecting data from different tables

I made a query that should output the stud_fname, stud_sname, result_studpoints, result_position and studevent_result for a specific requirements where stud_gender = ‘M’, stud_yrGroup = ‘9’ and event_id = ‘1’.

This is the query:

SELECT  stud_fname, stud_sname, result_studpoints, result_position, studevent_result FROM students
INNER JOIN result
WHERE stud_gender = 'M'
AND stud_yrgroup = '9'
AND result.stud_id IN (SELECT students.stud_id FROM result WHERE event_id = '1');

but it does not display the correct output.

The row Sam Cro should not be output because he does not do event_id = '1'. How can I fix my query so it outputs the correct data based on the requirements stated.

Thanks in advance.

Upvotes: 1

Views: 31

Answers (1)

Athanasios Kataras
Athanasios Kataras

Reputation: 26352

In the subquery in you are selecting the students.stud_id instead of the result which is the table you select from for event id 1. When you join, it is better to use the on clause instead of adding the check in the where clause. In general I would modify the query as follows.

SELECT  stud_fname, 
        stud_sname, 
        result_studpoints, 
        result_position, 
        studevent_result 
FROM 
    students s
        INNER JOIN 
    result r on 
    s.stud_is = r.stud_id
WHERE 
    stud_gender  = 'M'
AND stud_yrgroup = '9'
AND r.event_id   = '1';

You can also change the results table in the subquery to the students table to get the proper mapping but I would advise against it.

Upvotes: 1

Related Questions