CODEWITHSUNDEEP
mysqlsqldatabase
John Mathew
John Mathew

Reputation: 405

Sql for selecting from a table

I have a table like this 

Id      title           parentId    subparentId itemcategory

1       service cat1    0           0           C
2       service cat2    0           0           C
3       service subcat1 1           0           S
4       service subcat2 2           0           S
5       Item 1          1           0           I
5       Item 2          1           3           I
6       Item 3          2           4           I

I need an out put like this 

service cat1
        Item 1

    service subcat1
        Item 2

service cat2
    service subcat2
        Item 3

Ie, list shows the items ( category ,subcategory,Items ) in the order ascending order and if the items have any subcategory it should come under the subcategory

Upvotes: 1

Views: 77

Answers (3)

Rik
Rik

Reputation: 2032

Another way of doing it: http://sqlfiddle.com/#!9/bbf4d/1
(there is no need for a multiplier here)

select
  concat(indent1, indent2, title) as title
from (
select
  if(parentid>0,parentid,id) as id1,
  case itemcategory
    when 'C' then -1
    when 'S' then id
    when 'I' then if(subparentid>0,subparentid,0)
    end as id2,
  case itemcategory
    when 'C' then -1
    when 'S' then -1
    when 'I' then id
    end as id3,
  case itemcategory
    when 'C' then ''
    when 'S' then '-     - '
    when 'I' then '-     - '
  end as indent1,
  case itemcategory
    when 'C' then ''
    when 'S' then ''
    when 'I' then '-     - '
    end as indent2,
  title
from table1
order by id1,id2,id3
) allitems

Half of this code is for indenting so you get a nicer view. It's exactly like you requested (items are all indented equally even if they are not in the same level) but you can Fiddle with it yourself.

You can also add id1,id2,id3 in the first select to see how the order is done. The outside select is only done for viewing the title alone with indenting.

The result will be:

title
----------------------
service cat1
- - - - Item 1
- - service subcat1
- - - - Item 2
service cat2
- - service subcat2
- - - - Item 3

Upvotes: 1

Frederick Zhang
Frederick Zhang

Reputation: 3683

It's a complicated problem because there can be only one dimension in the sql query result.

But we can do a little trick here

SELECT *
FROM
  (
    SELECT
      id,
      title,
      parentId,
      subparentId,
      itemcategory,
      IF(
          parentId = 0 AND subparentId = 0,
          id * 10000,
          IF(
              subparentId = 0,
              parentId * 10000 + 100 - id,
              parentId * 10000 + subparentId * 100 + id
          )
      ) AS itemOrder
    FROM
      table1
  ) allOrder
ORDER BY allOrder.itemOrder

SQL Fiddle: http://sqlfiddle.com/#!9/5f711/1/0

Increase the multiplier if you've got more rows.

Upvotes: 1

cn0047
cn0047

Reputation: 17061

I think you should try something like:

SELECT
    t1.title,
    t2.title,
    t3.title
FROM table t1
LEFT JOIN table t2 ON t1.id = t2.parentId
LEFT JOIN table t3 ON t2.id = t3.subparentId
WHERE t1.itemcategory = 'C'
AND t2.itemcategory = 'S'
AND t3.itemcategory = 'I'
;

For this case: enter image description here

you should join your third table (item), directly with first table (service cat), not with second like in my example.

Upvotes: 1

Related Questions