Page 1 of 1

MySQL LIMIT on LEFT JOIN but only limiting one table

Posted: Fri Dec 15, 2006 5:28 am
by Chris Corbyn

Code: Select all

mysql> show create table fruits;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                      |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits | CREATE TABLE `fruits` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 | 
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table sales;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sales | CREATE TABLE `sales` (
  `id` int(11) NOT NULL auto_increment,
  `fruitid` int(11) default NULL,
  `customer` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select a.id, a.name, b.customer from fruits as a left join sales as b on b.fruitid = a.id;
+----+--------+----------+
| id | name   | customer |
+----+--------+----------+
|  1 | apple  | fred     | 
|  1 | apple  | joe      | 
|  1 | apple  | gemma    | 
|  1 | apple  | stuart   | 
|  2 | pear   | stuart   | 
|  2 | pear   | gemma    | 
|  2 | pear   | lisa     | 
|  3 | orange | fred     | 
|  4 | banana | NULL     | 
+----+--------+----------+
9 rows in set (0.00 sec)

mysql> select a.id, a.name, b.customer from fruits as a left join sales as b on b.fruitid = a.id order by a.name limit 2;
+----+-------+----------+
| id | name  | customer |
+----+-------+----------+
|  1 | apple | stuart   | 
|  1 | apple | gemma    | 
+----+-------+----------+
2 rows in set (0.00 sec)

mysql>
I want to limit the results returned to two *fruits* but not to just two rows. Any clues?

This is what I want to see:

Code: Select all

+----+--------+----------+
| id | name   | customer |
+----+--------+----------+
|  1 | apple  | fred     | 
|  1 | apple  | joe      | 
|  1 | apple  | gemma    | 
|  1 | apple  | stuart   | 
|  4 | banana | NULL     | 
+----+--------+----------+

Posted: Fri Dec 15, 2006 5:41 am
by jmut
subquery?

using limit on a subquery....and the result set is used in a LEFT JOIN afterwards.

Posted: Fri Dec 15, 2006 6:04 am
by Chris Corbyn

Code: Select all

select a.id, a.name, b.customer from fruits as a left join sales as b on b.fruitid = a.id where a.id in (select distinct c.id from fruits as c order by c.name limit 2) order by a.name;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
mysql>
:(

Posted: Fri Dec 15, 2006 6:04 am
by Chris Corbyn
This is MySQL 5 by the way.

Code: Select all

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.24a-Debian_9-log | 
+----------------------+
1 row in set (0.00 sec)

Posted: Fri Dec 15, 2006 6:22 am
by jmut

Code: Select all

SELECT a.id, a.name, b.customer 
    FROM  (select distinct c.id,c.name from fruits as c order by c.name limit 2) as a 
    LEFT JOIN sales as b on b.fruitid = a.id order by a.name;

Posted: Fri Dec 15, 2006 9:13 am
by Chris Corbyn
Awesome thanks, that works a peach! :)