ElmoTheClown Posted January 28, 2016 Share Posted January 28, 2016 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 Quote Link to comment Share on other sites More sharing options...
Zane Posted January 28, 2016 Share Posted January 28, 2016 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 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 28, 2016 Solution Share Posted January 28, 2016 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 | +--------------------+-----------+-----------+ 2 Quote Link to comment Share on other sites More sharing options...
ElmoTheClown Posted February 9, 2016 Author Share Posted February 9, 2016 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.