 |
|
 |
| |
Developer on focus
Anton Zamov is a dipl. engineer with more than 6 years of active professional
experience and many awards ...
read more>>
|
|
 |
 |
 |
|
|
|
|
| |
|
MySQL: Getting 1 matching row from an OUTER join
|
Getting 1 matching row from an OUTER join
CREATE TABLE parent (
pkey int(11) NOT NULL auto_increment,
pname varchar(5),
timeEnter timestamp,
PRIMARY KEY (pkey)
);
CREATE TABLE child (
ckey int(11) NOT NULL auto_increment,
pkey int(11) NOT NULL,
cname varchar(5),
timeEnter timestamp,
PRIMARY KEY (ckey)
);
insert into parent(pname) values ('A');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,'a1');
insert into child(pkey,cname) values (@a_lid,'a2');
insert into child(pkey,cname) values (@a_lid,'a3');
insert into parent(pname) values ('B');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,'b1');
insert into child(pkey,cname) values (@a_lid,'b2');
insert into parent(pname) values ('C');
mysql> select p.*,c.cname,count(c.pkey) as number
from parent p left outer join child c on p.pkey=c.pkey
where c.pkey is not null group by c.pkey;
+------+-------+---------------------+-------+--------+
| pkey | pname | timeEnter | cname | number |
+------+-------+---------------------+-------+--------+
| 5 | A | 2004-04-28 09:56:59 | a1 | 3 |
| 6 | B | 2004-04-28 09:56:59 | b1 | 2 |
+------+-------+---------------------+-------+--------+
2 rows in set (0.01 sec)
For comparison, here is a listing of all the matching data:
mysql> select * from parent p left outer join child c on p.pkey=c.pkey where c.pkey is not null;
select * from parent p left outer join child c on p.pkey=c.pkey where c.pkey is not null;
+------+-------+---------------------+------+------+-------+---------------------+
| pkey | pname | timeEnter | ckey | pkey | cname | timeEnter |
+------+-------+---------------------+------+------+-------+---------------------+
| 5 | A | 2004-04-28 09:56:59 | 7 | 5 | a1 | 2004-04-28 09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 8 | 5 | a2 | 2004-04-28 09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 9 | 5 | a3 | 2004-04-28 09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 10 | 6 | b1 | 2004-04-28 09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 11 | 6 | b2 | 2004-04-28 09:56:59 |
+------+-------+---------------------+------+------+-------+---------------------+
5 rows in set (0.00 sec)
|
About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GPU Free Documentation License)
Last Updated: Tue Jul 20 12:14:51 EDT 2004
|
|
|
|
|
|
|
|
