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>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 |
+----+--------+----------+