Jump to content

Grouping / Joining / Ordering


ElmoTheClown
Go to solution Solved by Barand,

Recommended Posts

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
Link to comment
Share on other sites

  • Solution

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 |
+--------------------+-----------+-----------+
  • Like 2
Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.