sql - How can I make this SELECT in hierarchical data modal using mysql? -

-

i have used hierarchical data modal store products in database. products have main , subcategory, , result when retrieving full tree of products.

select t1.name lev1,              t2.name lev2,               t3.name lev3, categories t1     left join categories t2          on t2.parent = t1.category_id     left join categories t3          on t3.parent = t2.category_id t1.name = 'products'  +----------+----------------------+-------------------+ | lev1     | lev2                 | lev3              | +----------+----------------------+-------------------+ | products | computers            | laptops           | | products | computers            | desktop computers | | products | computers            | tab pcs           | | products | computers            | crt monitors      | | products | computers            | lcd monitors      | | products | computers            | led monitors      | | products | mobile phones        | lg phone          | | products | mobile phones        | anroid phone      | | products | mobile phones        | windows mobile    | | products | mobile phones        | ipad              | | products | mobile phones        | samsung galaxy    | | products | digital cameras      | test              | | products | printers , toners  | null              | | products | test                 | abc               | | products | test2                | null              | | products | test3                | null              | | products | computer accessaries | usb cables        | | products | computer accessaries | network cables    | +----------+----------------------+-------------------+

my question need select level2 , level3 category id along select query.

i tried this: 

select t1.name lev1,                  t2.name lev2,                   t3.name lev3,                  t3.category_id categories t1     left join categories t2          on t2.parent = t1.category_id     left join categories t3          on t3.parent = t2.category_id t1.name = 'products'

but provide level3 ids. this. 

+----------+----------------------+-------------------+-------------+ | lev1     | lev2                 | lev3              | category_id | +----------+----------------------+-------------------+-------------+ | products | computers            | laptops           |           3 | | products | computers            | desktop computers |           4 | | products | computers            | tab pcs           |           5 | | products | computers            | crt monitors      |           6 | | products | computers            | lcd monitors      |           7 | | products | computers            | led monitors      |           8 | | products | mobile phones        | lg phone          |          10 | | products | mobile phones        | anroid phone      |          11 | | products | mobile phones        | windows mobile    |          12 | | products | mobile phones        | ipad              |          13 | | products | mobile phones        | samsung galaxy    |          14 | | products | digital cameras      | test              |          21 | | products | printers , toners  | null              |        null | | products | test                 | abc               |          20 | | products | test2                | null              |        null | | products | test3                | null              |        null | | products | computer accessaries | usb cables        |          23 | | products | computer accessaries | network cables    |          24 | +----------+----------------------+-------------------+-------------+

can tell me how can both level2 , level3?

if have approach if product has second level category not 3 level category, can try

select t1.name lev1,t2.name lev2, t3.name lev3,coalesce( t3.category_id, t2.category_id ) categories t1 left join categories t2 on t2.parent = t1.category_id left join categories t3 on t3.parent = t2.category_id t1.name = 'products'

which gives category id of level 2 when there isn't 3 level category


Comments

Post a Comment

Popular posts from this blog

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

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

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