mysql - pass field value into subquery

Question

In this case I am joining by a subquery and want to pass in the deptid to the joined subquery but im getting 'D is not defined' error message.

SELECT * 
    FROM(   
        SELECT D.name AS deptname,D.id AS deptid,WT.sortposition AS deptsortposition 
        FROM departments D JOIN web_taxonomy WT ON (WT.deptid=D.id AND WT.classid=0) 
        WHERE D.web=1
        ORDER BY sortposition
    ) AS D
    LEFT JOIN (
        SELECT C.name AS classname,C. id AS classid,C.department,WT.sortposition AS classsortposition,WT.deptid
        FROM classes C
        JOIN web_taxonomy WT ON (WT.classid=C.id AND WT.subclassid=0 AND WT.deptid=D.deptid) 
        WHERE web=1 ORDER BY classsortposition  
    ) AS C ON (C.department=D.deptid)

Any way to pass in the reference similar to what I wrapped in the strong tags above?

EDIT: I made a mistake and left in the working query originally and just added the part that I wanted to function. Essentially I want to minimize the size of the join subquery by already only getting the rows that share the same deptid as was found in the D subquery table.

Answer 1

You cannot use your alias "D" within the subquery you are aliasing.

This should work (just using X instead of D in the first subquery -not strictly necessary but helps with readability - and moving the reference to D to outside of the second subquery):

SELECT * 
    FROM(   
        SELECT 
           X.name AS deptname
           , X.id AS deptid
           , WT.sortposition AS deptsortposition 
        FROM departments X 
        JOIN web_taxonomy WT ON (WT.deptid=X.id AND WT.classid=0) 
        WHERE X.web=1
        ORDER BY sortposition
    ) AS D  -- this is available to objects referencing this alias
    LEFT JOIN (
        SELECT 
           C.name AS classname
           , C. id AS classid
           , C.department
           , WT.sortposition AS classsortposition
           , WT.deptid
        FROM classes C JOIN web_taxonomy WT 
        ON WT.classid=C.id AND WT.subclassid=0
        WHERE web=1 ORDER BY classsortposition  
    ) AS C ON C.department=D.deptid AND C.deptid = D.deptid -- i.e. here

Answer 2

I don't think you need to do subqueries at all:

SELECT D.name AS deptname, D.id AS deptid, WT1.sortposition AS deptsortposition,
  C.name AS classname, C.id AS classid, C.department, 
  WT2.sortposition AS classsortposition, WT2.deptid
FROM departments AS D
JOIN web_taxonomy AS WT1 ON (WT1.deptid=D.id AND WT1.classid=0)
LEFT OUTER JOIN web_taxonomy AS WT2 ON (WT2.deptid=D.id AND WT2.subclassid=0)
LEFT OUTER JOIN classes AS C ON (C.id=WT2.classid AND C.department=WT2.deptid);

The joins to WT1 should benefit from indexes:

ALTER TABLE web_taxonomy
  ADD KEY wt_dept_class (deptid, classid),
  ADD KEY wt_dept_subclass (deptid, subclassid);

The join to classes uses that table's PRIMARY key index.

Here's the output of EXPLAIN for this query:

+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys            | key           | key_len | ref              | rows | Extra                 |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+-----------------------+
|  1 | SIMPLE      | D     | ALL    | PRIMARY,id               | NULL          | NULL    | NULL             |    1 | NULL                  |
|  1 | SIMPLE      | WT1   | ref    | dept_class,dept_subclass | dept_class    | 10      | test.D.id,const  |    1 | Using index condition |
|  1 | SIMPLE      | WT2   | ref    | dept_class,dept_subclass | dept_subclass | 10      | test.D.id,const  |    1 | Using where           |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY,id               | PRIMARY       | 8       | test.WT2.classid |    1 | Using where           |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+-----------------------+

To tell the truth, I had to edit that EXPLAIN report a little bit to show that result. I tested with tables with zero rows, so the optimizer mistakenly chose the dept_class index for WT2. If you test with your real data, I think it'll choose the dept_subclass index correctly.

---

I tried your query, with a small modification to get around the error on D.deptid:

SELECT *
FROM(
  SELECT D.name AS deptname,D.id AS deptid,WT.sortposition AS deptsortposition
  FROM departments D JOIN web_taxonomy WT ON (WT.deptid=D.id AND WT.classid=0)
  WHERE D.web=1
  ORDER BY sortposition
) AS D
LEFT JOIN (
  SELECT C.name AS classname,C. id AS classid,C.department,WT.sortposition AS classsortposition,WT.deptid
  FROM classes C
  JOIN web_taxonomy WT ON (WT.classid=C.id AND WT.subclassid=0 AND WT.deptid=C.department)
  WHERE web=1 ORDER BY classsortposition
) AS C ON (C.department=D.deptid);

And the EXPLAIN report:

+----+-------------+------------+------+--------------------------+------------+---------+-----------------------------+------+----------------------------------------------------+
| id | select_type | table      | type | possible_keys            | key        | key_len | ref                         | rows | Extra                                              |
+----+-------------+------------+------+--------------------------+------------+---------+-----------------------------+------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL                     | NULL       | NULL    | NULL                        |    2 | NULL                                               |
|  1 | PRIMARY     | <derived3> | ALL  | NULL                     | NULL       | NULL    | NULL                        |    2 | Using where; Using join buffer (Block Nested Loop) |
|  3 | DERIVED     | C          | ALL  | PRIMARY,id               | NULL       | NULL    | NULL                        |    1 | Using where; Using temporary; Using filesort       |
|  3 | DERIVED     | WT         | ref  | dept_class,dept_subclass | dept_class | 10      | test.C.department,test.C.id |    1 | Using index condition; Using where                 |
|  2 | DERIVED     | D          | ALL  | PRIMARY,id               | NULL       | NULL    | NULL                        |    1 | Using where; Using temporary; Using filesort       |
|  2 | DERIVED     | WT         | ref  | dept_class,dept_subclass | dept_class | 10      | test.D.id,const             |    1 | Using index condition                              |
+----+-------------+------------+------+--------------------------+------------+---------+-----------------------------+------+----------------------------------------------------+

Yuck! It runs a tablescan for both departments and classes (that's the ALL in the type column), and it creates a temp table for each of the subqueries, and then joins them without any benefit of an index (that's what is meant by Using join buffer). Not a fun optimization plan to see.

In general, sorting should be the last part of the SQL query. Don't try to sort subquery results to work around the optimizer. You want the join to be assisted by an index if possible, but the order of the best index for the join isn't necessarily the order you want the final result to be returned in. So let the optimizer do its work to do the join, and then sort the final result afterwards.

SELECT D.name AS deptname, D.id AS deptid, WT1.sortposition AS deptsortposition,
  C.name AS classname, C.id AS classid, C.department,
  WT2.sortposition AS classsortposition, WT2.deptid
FROM departments AS D
JOIN web_taxonomy AS WT1 ON (WT1.deptid=D.id AND WT1.classid=0)
LEFT OUTER JOIN web_taxonomy AS WT2 ON (WT2.deptid=D.id AND WT2.subclassid=0)
LEFT OUTER JOIN classes AS C ON (C.id=WT2.classid AND C.department=WT2.deptid)
ORDER BY deptsortposition, classsortposition;

This still uses one temp table & filesort, but not two. And it avoids the join buffer; each join is index-assisted.

+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys            | key           | key_len | ref              | rows | Extra                           |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+---------------------------------+
|  1 | SIMPLE      | D     | ALL    | PRIMARY,id               | NULL          | NULL    | NULL             |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | WT1   | ref    | dept_class,dept_subclass | dept_class    | 10      | test.D.id,const  |    1 | Using where; Using index        |
|  1 | SIMPLE      | WT2   | ref    | dept_subclass            | dept_subclass | 10      | test.D.id,const  |    1 | Using where                     |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY,id               | PRIMARY       | 8       | test.WT2.classid |    1 | Using where                     |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+---------------------------------+