By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,341 Members | 1,395 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,341 IT Pros & Developers. It's quick & easy.

Joining parent with child table, only want one row of child (mySQL database)

P: n/a
I seem to always want to do this type of join, but I can never find
how to do this.

I am trying to join two tables. The parent table will always have one
row of data and the child may have 0 to 45 rows of data. I am trying
to join the tables in an outer join, so if 0 rows in child it is not
affected. But I only want one row returned of the child.

How do I do this in mySQL?

I have tried LIMIT, but this is not quite what I wanted.

Any help would be brilliant

Caroline
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Caroline" <ca*************@hotmail.com> wrote in message
news:77*************************@posting.google.co m...
I seem to always want to do this type of join, but I can never find
how to do this.

I am trying to join two tables. The parent table will always have one
row of data and the child may have 0 to 45 rows of data. I am trying
to join the tables in an outer join, so if 0 rows in child it is not
affected. But I only want one row returned of the child.


To get only "one row" .... group by

For instance, the following query:
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;

works on the data below:


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');
Ref (TIP 21)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

Regards,

Mike Chirico
Jul 20 '05 #2

P: n/a

"Caroline" <ca*************@hotmail.com> wrote in message
news:77*************************@posting.google.co m...
I seem to always want to do this type of join, but I can never find
how to do this.

I am trying to join two tables. The parent table will always have one
row of data and the child may have 0 to 45 rows of data. I am trying
to join the tables in an outer join, so if 0 rows in child it is not
affected. But I only want one row returned of the child.


To get only "one row" .... group by

For instance, the following query:
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;

works on the data below:


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');
Ref (TIP 21)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

Regards,

Mike Chirico
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.