MySQL LIMIT on LEFT JOIN but only limiting one table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

MySQL LIMIT on LEFT JOIN but only limiting one table

Post 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     | 
+----+--------+----------+
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

subquery?

using limit on a subquery....and the result set is used in a LEFT JOIN afterwards.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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>
:(
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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)
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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;
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Awesome thanks, that works a peach! :)
Post Reply