Outer select column value in joined subquery?
Posted
by Michael DePetrillo
on Stack Overflow
See other posts from Stack Overflow
or by Michael DePetrillo
Published on 2010-04-21T22:41:28Z
Indexed on
2010/04/21
23:03 UTC
Read the original article
Hit count: 122
mysql
|mysql-query
Is it possible to use a column value from an outer select within a joined 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;
This results in "Unknown column 'table1.lt' in 'where clause'".
Here is the 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);
© Stack Overflow or respective owner