Is there any way to make this code more efficient?..can't directly use IN coz version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

Question

Link that i referred to -> MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

I have a teacher that has a code and a mac address attached to the router.. the students have to enter the same code from the same mac address.. i want a list of students who have done it ... and i want to only check the last code uploaded by the teacher

thank you for all the help :)

My code(which is working)->

SELECT s.s_id
FROM students s
WHERE s.mac IN
    (SELECT *
     FROM
       (SELECT t.mac
        FROM teacher t
        ORDER BY t.sno DESC
        LIMIT 1)temp_tab)
  AND s.code IN
    (SELECT *
     FROM
       (SELECT t.code
        FROM teacher t
        ORDER BY t.sno DESC
        LIMIT 1)temp_tab);

Answer 1

Using a join should be more efficient, but then again, it depends on what you are trying to achieve.

SELECT s.s_id
FROM students s
JOIN (
    SELECT t.mac, t.code
    FROM teacher t
    ORDER BY t.sno DESC
    LIMIT 1
) t ON s.mac = t.mac AND s.code = t.code

Indexing the matching or ordering columns would increase the performance too.

Answer 2

You can use limit 1 with =:

SELECT s.s_id
FROM students s
WHERE s.mac = (SELECT t.mac
               FROM teacher t
               ORDER BY t.sno DESC
               LIMIT 1
              ) AND
     s.code = (SELECT t.code
               FROM teacher t
               ORDER BY t.sno DESC
               LIMIT 1
              );

However, this code is probably not what you want. It is fine if there are no ties for t.sno. But if they are the two fields may not come from the same row. So, do it in one subquery:

SELECT s.s_id
FROM students s
WHERE (s.mac, s.code) = (SELECT t.mac, t.code
                         FROM teacher t
                         ORDER BY t.sno DESC
                         LIMIT 1
                        );

You can, of course, turn this into a JOIN as Viliam suggests.

Answer 3

instead of select s.s_id it should be * Thanks Gordon and Villiam

SELECT * FROM students s JOIN ( SELECT t.mac, t.code FROM teacher t ORDER BY t.sno DESC LIMIT 1 ) t ON s.mac = t.mac AND s.code = t.code

Answer 4

Why does everyone think they have to run every SQL task in a single query?

Here's a much easier solution:

SELECT t.mac, t.code
FROM teacher t
ORDER BY t.sno DESC
LIMIT 1
INTO @mac, @code;

SELECT s.s_id
FROM students s
WHERE s.mac = @mac AND s.code = @code;