MySQL Subquery Repetition Avoidance in WHERE/AND

Question

I have a SQL query, where I'm using a subquery in a WHERE clause. I then need to use the same subquery again to compare it to a different column.

I'm assuming there isn't a way for my to access 'emp_education_list li' outside of the subquery?

I'm guessing the subquery repetition is redundant. I'm a bit rusty in SQL to be frank though.

SELECT e.fname, e.lname
    FROM employee e, emp_education_list l
    WHERE
        e.skillsID = l.skillsID
    AND
    (
        SELECT li.educationID
            FROM emp_education_list li, job j
            WHERE j.skillsID = li.skillsID
            AND j.jobID = 1001
    ) = l.educationID
    AND
    (
        SELECT li.edu_level
            FROM emp_education_list li, job j
            WHERE j.skillsID = li.skillsID
            AND j.jobID = 1001
    ) < l.edu_level
;

Answer 1

Give this a try,

SELECT  e.fname, e.lname
FROM    employee e 
        INNER JOIN emp_education_list l
            ON e.skillsID = l.skillsID
        INNER JOIN
        (
            SELECT  li.educationID, li.edu_level
            FROM    emp_education_list li
                    INNER JOIN job j 
                        ON j.skillsID = li.skillsID
            WHERE   j.jobID = 1001
        ) x ON  l.educationID = x.educationID
WHERE   x.edu_level < l.edu_level