Name of child's parent

#1

I'm not sure how to even ask about this, nor how to phrase this for
Google, but here it goes.

I have a table, category, eg:

CREATE TABLE category
(id INT AUTO_INCREMENT,
category VARCHAR(20),
children VARCHAR(2),
parent INT,
PRIMARY KEY(id));

INSERT INTO category
(category, children, parent)
VALUES
('Bakery','Y',NULL),
('Cakes',NULL,1),
('Pies',NULL,1),
('Auto','Y',NULL),
('Repair',NULL,4),
('Sales',NULL,4);

What I want to see is something like:

Bakery
Bakery - Cakes
Bakery - Pies
Auto
Auto - Repair
Auto - Sales

What I am doing now is:

SELECT id, category FROM category WHERE childen = 'Y'

and as I loop through the result set, I am doing yet another query:

SELECT id, category FROM category WHERE parent = "parentid"

It seems there must be a better way to do this. TIA for any help or
ideas.

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

#2

On 2011-03-08 06:46, Adrienne Boswell wrote:

Quote:

I'm not sure how to even ask about this, nor how to phrase this for
Google, but here it goes.

I have a table, category, eg:

CREATE TABLE category
(id INT AUTO_INCREMENT,
category VARCHAR(20),
children VARCHAR(2),
parent INT,
PRIMARY KEY(id));

INSERT INTO category
(category, children, parent)
VALUES
('Bakery','Y',NULL),
('Cakes',NULL,1),
('Pies',NULL,1),
('Auto','Y',NULL),
('Repair',NULL,4),
('Sales',NULL,4);

What I want to see is something like:

Bakery
Bakery - Cakes
Bakery - Pies
Auto
Auto - Repair
Auto - Sales

What I am doing now is:

SELECT id, category FROM category WHERE childen = 'Y'

and as I loop through the result set, I am doing yet another query:

SELECT id, category FROM category WHERE parent = "parentid"

It seems there must be a better way to do this. TIA for any help or
ideas.

I assume Bakery the rows with one column is some kind of header, and
that should IMO be part of presentation, not logic.

select x.category, y.category as child_category
from category x
join category y
on y.parent = x.id
order by category, child_category;
+----------+----------------+

Quote:

4 rows in set (0.00 sec)

As you can see the children column is redundant and can be removed. If
you keep it and try to maintain it you face the risk of introducing
anomalies.

If you insist on retriveing the header rows it can be done something like:

select x.category, y.category as child_category
from category x
join category y
on y.parent = x.id
union
select category, ''
from category x
where not exists (
select 1 from category y
where x.parent = y.id
)
order by category, child_category;
+----------+----------------+

Quote:

6 rows in set (0.00 sec)

/Lennart

#3

On Mar 8, 5:46*am, Adrienne Boswell <arb... (AT) yahoo (DOT) com> wrote:

Quote:

I'm not sure how to even ask about this, nor how to phrase this for
Google, but here it goes.

I have a table, category, eg:

CREATE TABLE category
(id INT AUTO_INCREMENT,
category VARCHAR(20),
children VARCHAR(2),
parent INT,
PRIMARY KEY(id));

INSERT INTO category
(category, children, parent)
VALUES
('Bakery','Y',NULL),
('Cakes',NULL,1),
('Pies',NULL,1),
('Auto','Y',NULL),
('Repair',NULL,4),
('Sales',NULL,4);

What I want to see is something like:

Bakery
Bakery - Cakes
Bakery - Pies
Auto
Auto - Repair
Auto - Sales

What I am doing now is:

SELECT id, category FROM category WHERE childen = 'Y'

and as I loop through the result set, I am doing yet another query:

SELECT id, category FROM category WHERE parent = "parentid"

It seems there must be a better way to do this. *TIA for any help or
ideas.

--
Adrienne Boswell at Home
Arbpen Web Site Design Serviceshttp://www.cavalcade-of-coding.info
Please respond to the group so others can share

Hi Adrienne, take a look at the links here:
http://www.delicious.com/Captain_Paralytic/hierarchical

#4

Gazing into my crystal ball I observed Captain Paralytic
<paul_lautman (AT) yahoo (DOT) com> writing in news:850629c2-aad9-42a1-b378-
53297ab2305f (AT) s3g2000vbf (DOT) googlegroups.com:

Quote:

Hi Adrienne, take a look at the links here:
http://www.delicious.com/Captain_Paralytic/hierarchical

Wow! Thank you, Captain, lots of stuff to go through. I've bookmarked
that and put on a pot of Cuban coffee.

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

#5

Gazing into my crystal ball I observed Lennart Jonsson
<erik.lennart.jonsson (AT) gmail (DOT) com> writing in
news:il4m4k$nkl$1 (AT) news (DOT) eternal-september.org:

Quote:

On 2011-03-08 06:46, Adrienne Boswell wrote:
I'm not sure how to even ask about this, nor how to phrase this for
Google, but here it goes.

I have a table, category, eg:

CREATE TABLE category
(id INT AUTO_INCREMENT,
category VARCHAR(20),
children VARCHAR(2),
parent INT,
PRIMARY KEY(id));

INSERT INTO category
(category, children, parent)
VALUES
('Bakery','Y',NULL),
('Cakes',NULL,1),
('Pies',NULL,1),
('Auto','Y',NULL),
('Repair',NULL,4),
('Sales',NULL,4);

What I want to see is something like:

Bakery
Bakery - Cakes
Bakery - Pies
Auto
Auto - Repair
Auto - Sales

What I am doing now is:

SELECT id, category FROM category WHERE childen = 'Y'

and as I loop through the result set, I am doing yet another query:

SELECT id, category FROM category WHERE parent = "parentid"

It seems there must be a better way to do this. TIA for any help or
ideas.

I assume Bakery the rows with one column is some kind of header, and
that should IMO be part of presentation, not logic.

select x.category, y.category as child_category
from category x
join category y
on y.parent = x.id
order by category, child_category;
+----------+----------------+
| category | child_category |
+----------+----------------+
| Auto | Repair |
| Auto | Sales |
| Bakery | Cakes |
| Bakery | Pies |
+----------+----------------+
4 rows in set (0.00 sec)

As you can see the children column is redundant and can be removed. If
you keep it and try to maintain it you face the risk of introducing
anomalies.

If you insist on retriveing the header rows it can be done something
like:

select x.category, y.category as child_category
from category x
join category y
on y.parent = x.id
union
select category, ''
from category x
where not exists (
select 1 from category y
where x.parent = y.id
)
order by category, child_category;
+----------+----------------+
| category | child_category |
+----------+----------------+
| Auto | |
| Auto | Repair |
| Auto | Sales |
| Bakery | |
| Bakery | Cakes |
| Bakery | Pies |
+----------+----------------+
6 rows in set (0.00 sec)

/Lennart

Thank you so much, that's exactly what I needed. I am going to read up
on what Captain Paralytic said as well, but for now, your second option
is right on target.

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Name of child's parent

comp.databases.mysql comp.databases.mysql

Name of child's parent - 03-07-2011 , 11:46 PM

Re: Name of child's parent - 03-08-2011 , 01:33 AM

Re: Name of child's parent - 03-08-2011 , 04:14 AM

Re: Name of child's parent - 03-08-2011 , 08:39 AM

Re: Name of child's parent - 03-08-2011 , 04:20 PM