Best way to reference an outer query / subquery?

Question

I'm trying to reference a field from the 1st select table in the 3rd select(subquery) table. However, that field isn't recognized when it goes to that sub-level of a query. The php code I'm working on uses sql to return part of the sql command (string) that will be used in other places.

I've came up with this example that shows up the kind of nested querys that I want to solve. In here I'm trying to get the name and emails of users that are working at night and have a matching job rank for an available job:

tables -----------> fields
table_users      -> [user_id, name, email, rank, ...]
table_users_jobs -> [user_id, job_id, period, ....]
table_jobs       -> [job_id, status, rank, ...]


-- sql calling code -> $rank = "t1.rank"; get_users_info_by_rank($rank);
-- maybe using: SET @rank = NULL; SELECT @rank := $rank, t1.name, ...
   SELECT t1.name, t1.email
   FROM table_users as t1
   WHERE t1.user_id IN (
       SELECT t2.user_id
       FROM table_users_jobs as t2
       WHERE t2.period = 'night' AND
         t2.job_id IN (
--         avaiable jobs to that rank -> get_job_ranks_sql($rank);
           SELECT t3.job_id 
           FROM table_jobs as t3
--         maybe using: t3.rank = @rank
           WHERE t3.rank = t1.rank AND
            t3.status = 'avaiable_position')
       )

Working a little I guess I could avoid the 3rd level select problem. Nevertheless the point is that I'm trying to reuse sql code like the function that gives me the job_id of the rank that I chose:

  function get_job_ranks_sql($rank){
    //probably 't3' will be renamed for something more unique
    return 'SELECT t3.job_id 
            FROM table_jobs as t3
            WHERE t3.rank = '.$rank.' AND
            t3.status = "available_position")';
  }

Even using php I'm trying to make it generic to maybe use with another language if possible. The sql version using is MySQL 5.1.41

Actually I think it's possible the way I want, by using sql variables like @rank, but I'm not sure if it's slower and if there are other better ways to do it.

Thanks in advance for any help :)

Answer 1

So, as one commenter pointed out, I think you would do much better off using JOINS, than sub-selects. For example, if I am reading your query/problem correctly, you could do a join query like this:

SELECT t1.name, t1.email, t3.job_id
FROM table_users t1
LEFT JOIN table_users_job t2
ON t1.user_id = t2.user_id
LEFT JOIN table_jobs t3
ON t3.job_id = t2.job_id
WHERE t2.period = 'night
AND t3.status = 'available_position'

Which is a lot more concise, easier to read, and is easier on your database. But doing this would prevent you from modularizing your SQL. If that is really important, you might consider storing such queries in Stored Procedure. This way, you can actually get a SP to return a list of results. Take a look at this tutorial:

http://www.wellho.net/resources/ex.php4?item=s163/stp4

Of course, that doesn't really solve your problem of being able to access variables at the lower levels of a sub select, but it would make your SQL easier to manage, and make it available to other language implementations, as you mentioned might be a need for you.

Something else to consider, in the bigger picture, would be migrating to a PHP framework that provides an ORM layer, where you could make those tables into objects, and then be able to access your data with much greater ease and flexibility (usually). But that is very 'big picture' and might not be suitable for your project requirements. One such framework that I could recommend, however, is CakePHP.