Grouping / Joining / Ordering

[ElmoTheClown]

Howdy,

 

I'm trying to get a result from my database in the proper order. 

 

CREATE TABLE IF NOT EXISTS `clc_crz_regions` (
  `region_id` tinyint(2) NOT NULL,
  `parent_id` tinyint(2) NOT NULL DEFAULT '0',
  `regAbbreviation` varchar(4) NOT NULL DEFAULT '',
  `reg_name` varchar(40) NOT NULL DEFAULT '',
) 

 

INSERT INTO `clc_crz_regions` (`region_id`, `parent_id`, `regAbbreviation`, `reg_name`) VALUES
(4, 0, 'soam', 'South America'),
(1, 0, 'cari', 'Caribbean'),
(20, 1, 'eaca', 'Eastern Caribbean'),
(44, 1, 'soca', 'Southern Caribbean'),
(21, 1, 'weca', 'Western Caribbean'),
(41, 4, 'amri', 'Amazon River');


CREATE TABLE IF NOT EXISTS `clc_crz_region_groups` (
  `parent_id` tinyint(2) NOT NULL,
  `child_id` tinyint(2)NOT NULL
)


INSERT INTO `clc_crz_region_groups` (`parent_id`, `child_id`) VALUES
(1, 1),
(1, 20),
(1, 21),
(1, 44),
(2, 2),
(3, 3),
(3, 40),
(3, 57),
(4, 4),

I'd like it to sort by 'reg_name' for the parents, with the children under the parents also sorted by name.

Caribbean

Eastern Caribbean

Southern Caribbean

Western Caribbean

South America

Amazon River

Cape Horn

 

I can get the results, just can't get them to order the way I want to.

SELECT r1.region_id, r1.reg_name, r1.reg_entry_name, r1.parent_id FROM $wpdb->crz_regions r1 
JOIN $wpdb->crz_region_groups r2 ON r1.region_id = r2.child_id 

I just can't get it sorted correctly. I'd like to do it in SQL if possible.

Thank you

[Zane]

Um.. use the ORDER BY clause

 

This is untested.

SELECT r1.region_id, r1.reg_name, r1.reg_entry_name, r1.parent_id FROM $wpdb->crz_regions r1 
JOIN $wpdb->crz_region_groups r2 ON r1.region_id = r2.child_id
ORDER BY r1.region_name

[Barand]

What is the group table for when you have the region parent in the region table?

 

Try

SELECT reg_name as region
, region_id
, parent_id 
FROM clc_crz_regions
WHERE parent_id=0
UNION
SELECT r1.reg_name
, r2.region_id
, r1.parent_id
FROM clc_crz_regions r1
INNER JOIN clc_crz_regions r2 ON r1.parent_id = r2.region_id
WHERE r2.parent_id=0
ORDER BY region_id, parent_id, region

+--------------------+-----------+-----------+
| region             | region_id | parent_id |
+--------------------+-----------+-----------+
| Caribbean          |         1 |         0 |
| Eastern Caribbean  |         1 |         1 |
| Southern Caribbean |         1 |         1 |
| Western Caribbean  |         1 |         1 |
| South America      |         4 |         0 |
| Amazon River       |         4 |         4 |
+--------------------+-----------+-----------+

[ElmoTheClown]

Thank you!

 

I had to put this aside for a bit to get a grip on sanity. Beat my head on this for a while.

 

The second table idea came from an article on closure tables. I couldn't get what I wanted with a single table. 

 

Now that I have a working example of Union working correctly on a website, that will help too. 

 

Sincerest appreciation,

Joe