CODEWITHSUNDEEP
phpmysqlsqljoin
Ahmar Ali
Ahmar Ali

Reputation: 1068

Inner Join limit left table results limit to 1

I have two tables. One is students and other one is results. Now student id in studetnt's table is unique but one student can have multiple rows in results table.

I want to join both tables and produce exactly one resulting row for every student. This row will contain student's info (name,image etc) and most recent student result.

I have applied inner join to achieve this but I am getting separate row for each entry in results table. If I apply limit 1 it gives me result with only one student's data and not every student's.

Here is the query: 

SELECT students.id,
       students.roll_num,
       students.first_name,
       students.middle_name,
       students.last_name,
       students.course,
       students.photo_url,
       results.parano,
       results.grade
FROM students
RIGHT JOIN results ON results.std_id=students.id

And here is what I get in results:

enter image description here

Upvotes: 1

Views: 115

Answers (3)

Andrzej Reduta
Andrzej Reduta

Reputation: 767

recent student result is a key. This will complicate query... 

SELECT
    st.id,
    st.roll_num,
    st.first_name,
    st.middle_name,
    st.last_name,
    st.course,
    st.photo_url,
    rs1.parano,
    rs2.grade 
FROM students st, results rs1, results rs2 
WHERE rs1.std_id=st.id AND rs2.std_id=st.id 
GROUP BY st.id
HAVING 
        count(rs1.parano) = 
        (SELECT count(rs.parano) 
        FROM results rs 
        WHERE rs.std_id=st.id 
        ORDER BY count(rs.parano) 
        LIMIT 1)
AND     count(rs2.grade) = (
        SELECT count(rs.grade) 
        FROM results rs 
        WHERE rs.std_id=st.id 
        ORDER BY count(rs.grade) 
        LIMIT 1)

Upvotes: 2

Deva
Deva

Reputation: 443

Assuming you have a date field in results table. The best way is to filter the results first and then perform the join.

select
    students.id,
    students.roll_num,
    students.first_name,
    students.middle_name,
    students.last_name,
    students.course,
    students.photo_url,
    new_results.parano,
    new_results.grade 
from students inner join 
    (select std_id, parano, grade, max(result_date) 
         from results group by std_id ) 
     as new_results
on students.id = new_results.id;

Upvotes: 1

potashin
potashin

Reputation: 44581

select
    students.id,
    students.roll_num,
    students.first_name,
    students.middle_name,
    students.last_name,
    students.course,
    students.photo_url,
    results.parano,
    results.grade 
from students 
    join results on results.std_id=students.id 
group by students.id

Upvotes: 1

Related Questions