2

I wanted to write several mysql queries to retrieve data from mysql database.

If I explain the problem, there are 136 columns in the data base table. Table is used to store students' information and their skill categories. There are about 6 main categories and all 136 sub categories lay withing them. If the main category is Mathematics there are subcategories such as Applied maths, Pure Mathematics and etc. All the information are included in one table and there is no normalization.

Students' skill level is measured from 1-5. 5 is maximum. If I passe a student Name in to mysql query, I want to print his main categories and sub categories in descending order. Please note that any student can follow more than one major category.

Last Name       Web technologies - HTML Web technologies - CSS  Web technologies - XML  Web technologies - JavaScript   Web technologies - J-Query  ......
Bill            3   1   4   na  na  1   3   na  na  na  na  na  na  na  na  na
Arun            1   5   1   3   1   2   1   na  na  3   1   5   1   1   na
Soma            na  na  na  na  na  na  na  na  na  na  na  na  na  na  na  na

If I select Arun, I want to show skill level as 5 5 3 3 3 1111111 and respective skill category.

wordpressm
  • 3,189
  • 3
  • 20
  • 29
  • 3
    If you provide sample data(not all 100+ columns, just few of them), and what do you expect to get, it will help everybody to understand your problem – Uriil Jun 10 '13 at 15:05
  • You can use http://sqlfiddle.com/ to create a dummy data sets.and it will be lot easier for us to provide a solution then – swapnesh Jun 10 '13 at 15:06
  • 1
    What type of "descending order", by skill level, chronological, alphabetical? I know you are new to SO but to get a specific answer we need a specific question, please provide a small data set and any relevant code that you have tried. – Devon Bernard Jun 10 '13 at 15:08
  • I have updated. Thanks. – wordpressm Jun 10 '13 at 15:12
  • 1
    This is a really horrible schema design. You should use a table with columns `student_id`, `skill_id`, `skill_level`. – Barmar Jun 10 '13 at 15:22
  • 2
    you're storing data in your db structure, and now you need to do a pivot query. http://stackoverflow.com/questions/6605604/mysql-pivot-query-results-with-group-by IMHO you should seriously reconsider your schema. – David Chan Jun 10 '13 at 15:23
  • `If I explain the problem, there are 136 columns in the data base table` - right there is your problem. – Burhan Khalid Jun 10 '13 at 15:35
  • Yes I accept it is not good db schema but It was difficult to handle 136 columns and originally it was a excel file, but I would reconsider the suggestion and try to normalize if I can't manage this way. – wordpressm Jun 10 '13 at 15:35

1 Answers1

1
SELECT *
FROM (SELECT "Web Technologies HTML", skill_web_html skill_level
      FROM student_skills
      WHERE student_id = @id
      UNION
      SELECT "Web Technologies CSS", skill_web_css
      FROM student_skills
      WHERE student_id = @id
      UNION
      ...
      SELECT "Web Technologies J-Query", skill_web_jquery
      FROM student_skills
      WHERE student_id = @id
      UNION
      ...) all_skills
ORDER BY skill_level DESC
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • 1
    what if I select all data set from mysql query and use handle it from programming language to sort, filter the data? I think what you sujjest is good but take time since I have to deal with each and every attribute of the relation. – wordpressm Jun 11 '13 at 04:56
  • 1
    That's a fine solution. It should be easy to do using the PHP `sort()` function. – Barmar Jun 11 '13 at 04:59