CODEWITHSUNDEEP
mysqlsqljoinsubquery
user3006222
user3006222

Reputation: 95

SQL RIGHT JOIN on Subquery isn't working

I have two tables containing over a million rows each. The two tables look something this like this; Table1

student_id     student_name
001            mark
002            moses
003            mike
004            john
005            jeff
006            joe

and Table2

student_id   height   weight
001          170      80      
002          130      88
003          190      90
004          150      75
005          200      85
006          180      78

What is want to is to return students with top 4 weights if their height is between 150cm and 200cm; something like this

student_id      student_name         weight
003             mike                 90 
005             jeff                 85
001             mark                 80
006             joe                  78

I have tried with something like this but it doesn't work;

SELECT a.student_id AS `Student ID`, a.student_name AS `Student Name`, b.weight
FROM table1 AS a
RIGHT JOIN 
    (
        SELECT student_id 
        FROM table2 
        WHERE height BETWEEN 150 AND 200 ORDER BY weight DESC LIMIT 4
    ) AS b
ON b.student_id
WHERE a.student_id IN b.student_id

Any approach that achieves the required result is highly appreciated. Thanks in advance!

Upvotes: 2

Views: 401

Answers (2)

Zak
Zak

Reputation: 7515

I would have used a LEFT JOIN IE

SELECT a.student_id, a.student_name, b.weight 
FROM table1 a
  LEFT JOIN table2 b
  ON a.student_id = b.student_id
WHERE b.hieght 
   BETWEEN 150 
   AND 200 
ORDER BY b.weight DESC LIMIT 4;

Upvotes: 0

David
David

Reputation: 153

I use SQL Server but I suppose this should work...

SELECT table2.student_id as 'Student ID',
       table1.student_name AS 'Student Name', 
       table2.weight
FROM table2 
INNER JOIN table1 ON table1.student_id = table2.student_id 
WHERE table2.height BETWEEN 150 AND 200 
ORDER BY table2.weight DESC 
LIMIT 4

Upvotes: 2

Related Questions