MySQL- Selecting data from different tables

Question

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.

Athanasios Kataras · Accepted Answer · 2016-03-19 15:08:20Z

1

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.

edited Mar 19, 2016 at 15:08

answered Mar 19, 2016 at 15:02

Athanasios Kataras

26.7k6 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL- Selecting data from different tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related