sql - Mysql query using group_concat and self referencing id in the table -

-

here table , sample data. 

create table `sections` (   `section_id` int(11) not null auto_increment,   `name` varchar(100) not null,   `parent_section_id` int(11) default null,   primary key (`section_id`) );  insert `sections` (`section_id`, `name`, `parent_section_id`) values (1, 'city', null), (2, 'supplements', 4), (3, 'news', 5), (4, 'sunday', 2), (5, 'monday', 2), (6, 'tuesday', 2), (7, 'wednesday', 2), (8, 'thursday', 2), (9, 'friday', 2), (10, 'saturday', 2), (11, 'home', 4), (12, 'games', 4), (13, 'sites', 5), (14, 'sports', 5), (15, 'cyber space', 6);

parent_section_id foreign key referencing section_id in same table can have null if doesn't belong other section.

how can below output have tried using group_concat function doesn't give exact result. parent_section_id pointing id same table. should use other column achieve below output or use other table keep track of sections contains sub sections. please me solve problem or suggest other approach

id, name, subsections ---------------------- 1, 'city', null 2, 'supplements', 'sunday,monday,tuesday,wednesday,thursday,friday,saturday' 3, 'news', null 4, 'sunday', 'homes,games' 5, 'monday','sites,sports' 6, 'tuesday', 'cyber space' 7, 'wednesday', null 8, 'thursday', null 9, 'friday', null 10, 'saturday', null 11, 'home', null 12, 'games', null 13, 'site', null 14, 'sports', null 15, 'cyber space',null

here sql fiddle link http://sqlfiddle.com/#!9/e9767/2

final query

select s1.section_id, s1.name, group_concat(s2.name) subsections, (select name sections section_id = s1.parent_section_id) 'parentname' sections s1 left join sections s2 on s1.section_id = s2.parent_section_id group s1.section_id;

you can result want using (left) self-join on section_id = parent_section_id so:

select s1.section_id, s1.name, group_concat(s2.name) subsections sections s1 left join sections s2 on s1.section_id = s2.parent_section_id group s1.section_id;

sample sql fiddle


Comments

Post a Comment

Popular posts from this blog

php - Invalid Cofiguration - yii\base\InvalidConfigException - Yii2 -

-
Image
i've installed yii2-user module/dektrium yii2-app-basic application command composer require "dektrium/yii2-user:0.9.*@dev" config/console.php return [ . . 'modules' => [ 'gii' => 'yii\gii\module', 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] config/web.php 'components' => [ . . /* 'user' => [ 'identityclass' => 'app\models\user', 'enableautologin' => true, ], */ 'modules' => [ 'user' => [ 'class' => 'dektrium\user\module', ], ], . . ] after that, run command $ php yii migrate/up --migrationpath=@vendor/dektrium/yii2-user/migrations updating database schema . but, when run http://localhost/mylawsuit/yii/web/index.php?r=u
Read more

How to show in django cms breadcrumbs full path? -

-
need advice - how show in breadcrumbs whole path article or page? i've got code: {% ance in ancestors %} <li> {% if not forloop.last %} <a href="{{ ance.get_absolute_url }}">{{ ance.get_menu_title }}</a> {% else %} <span class="active">{{ ance.get_menu_title }}</span> {% endif %} </li> {% endfor %} but shows "home" , current page. how modify show whole path in breadcrumbs? i've found in file menu_tags.py code calls ancestors. @ least there class showbreadcrumbs: class showbreadcrumb(inclusiontag): """ shows breadcrumb node has same url current request - start level: after level should breadcrumb start? 0=home - template: template used render breadcrumb """ name = 'show_breadcrumb' template = 'menu/dummy.html' options = options( argument('start_level', default=0, require
Read more

ruby on rails - npm error: tunneling socket could not be established, cause=connect ETIMEDOUT -

-
i have app uses rails framework , implements angularjs part of front end. i have pushed heroku , have heroku toolbelt installed, when try migrate db using "heroku run rake db:migrate" receive following error(s): installing core plugins heroku-cli-addons, heroku-apps, heroku-fork, heroku-git, heroku-local, heroku-run, heroku-status... error installing package. try running again gode_debug=info see more output. ! `run` not heroku command. ! perhaps meant `-h`, `2fa`, `auth`, `join`, `open`, `orgs`, `pg`, `ps` or `rake`. ! see `heroku help` list of available commands. i run command "gode_debug=info heroku run rake db:migrate" , receive error: npm err! darwin 14.5.0 npm err! argv "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/bin/node" "/users/christopher_pelnar/.heroku/node-v4.2.1-darwin-x64/lib/node_modules/npm/cli.js" "install" "heroku-cli-addons" "heroku-apps" "heroku-fork
Read more