The value of the outer selection column in the combined subquery?

Question

Is it possible to use a column value from an external selection in a combined subquery?

SELECT table1.id, table2.cnt FROM table1 LEFT JOIN (SELECT COUNT(*) as `cnt` FROM table2 where table2.lt > table1.lt and table2.rt < table1.rt) as table2 ON 1;

As a result, the "Unknown column" table1.lt "appears in the" where "section.

Here is a db dump.

CREATE TABLE IF NOT EXISTS `table1` ( `id` int(1) NOT NULL, `lt` int(1) NOT NULL, `rt` int(4) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `table2` ( `id` int(1) NOT NULL, `lt` int(1) NOT NULL, `rt` int(4) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table1` (`id`, `lt`, `rt`) VALUES (1, 1, 4);

INSERT INTO `table2` (`id`, `lt`, `rt`) VALUES (2, 2, 3);

Answer 1

Your internal query is a correlated subquery, but it does not see table1 at all. This is a limitation on MySQL - see the MySQL Manual - D.3. Subquery Restrictions . About halfway up he points out:

FROM . ( ) , .

LEFT JOIN, FROM.

:

SELECT table1.id, 
       (SELECT COUNT(*) FROM table2 where table2.lt > table1.lt and table2.rt < table1.rt) as cnt
FROM table1;