0

I have the following database structure:

Database Structure

And the data stored inside is as follows:

test_summary

test_details

However, I like to select data from this database in the following format: What I Want

How can I use one single SELECT statement to achieve this? If using one single SELECT statement cannot, how to achieve this by whatever sql code? Can someone give me the implementation?

2342G456DI8
  • 1,819
  • 3
  • 16
  • 29
  • how many different `m1-t1,m1-t2...` are possible and using sql it can not be done exactly how you listed out however it could be achieved to some extent. – Abhik Chakraborty May 25 '15 at 08:34
  • @AbhikChakraborty, `m1-t1, m1-t2...` is not a fixed length. For instance, it could be `m1-t1, m1-t2, m1-t3, m2-t1, m2-t2, m3-t1`. The table I want should be organized by `m`. – 2342G456DI8 May 25 '15 at 08:37
  • Your desired result is nothing but pivot data and for known set of item its easy however in your case you need dynamic sql to achieve it. Check for mysql dynamic pivot table. – Abhik Chakraborty May 25 '15 at 08:40
  • `x_value` is also not limited or fixed to 0, 10 and 20? – DangeMask May 25 '15 at 08:43
  • @DangeMask, yes, to simplify the problem, let's assume they are fixed to `0, 10, 20` first. – 2342G456DI8 May 25 '15 at 08:45

2 Answers2

1

Can I use one single SELECT statement to achieve this?

Yes, you can. You will have to play a little with JOIN and GROUP BY statement, but it can be achieved.

EDIT:

Let's try multiple JOIN statements:

Select sum.name, x0.y_value, x10.y_value, x20.y_value
    from test_summary as sum
    join test_details as x0 on sum.id=x0.id and x0.x_value=0
    join test_details as x10 on sum.id=x10.id and x10.x_value=10
    join test_details as x20 on sum.id=x20.id and x20.x_value=20

Based on answers to multiple joins and join with where clause

Community
  • 1
  • 1
DangeMask
  • 531
  • 4
  • 19
  • Your question was, if it can be achieved ;-) On second look, it is not so easy as I thought. I'll think about it for a while and try to find solution. – DangeMask May 25 '15 at 08:44
  • Please try my new suggestion. I'm more familiar with MS SQL so I hope my syntax is correct. – DangeMask May 25 '15 at 12:11
  • the solution you provide only works when `x_value` instance is small. If the `x_value` ranges from 0 to 100 with step 10, `0, 10, 20, 30, 40...90, 100`. This `SELECT` statement takes forever! – 2342G456DI8 May 26 '15 at 03:09
  • I asked you if it is limited. This is the reason why. I'm afraid my experience is limited to this solution. – DangeMask May 26 '15 at 03:37
  • You can also try to create indexes over detail table to speed up searching. But I'm not sure if it will significantly help. – DangeMask May 26 '15 at 03:43
  • I tried using the `index`. I put `x_value` as the index. But the speed doesn't change. It basically never are going to finish! – 2342G456DI8 May 26 '15 at 05:47
  • Is the number of x_values constant? How fast does the values change? Is this select the only use of this data? I'm trying to understand your needs. Personaly, I don't like this kind of table structure, but maybe you have the reason to use it as it is. – DangeMask May 26 '15 at 06:44
  • thanks for the follow up. Frankly speaking, this is the better table structure I can think of right now... And the key concern for me to come up with this structure is that the number of `x_values` is not fixed all time. It could be any value basically. – 2342G456DI8 May 26 '15 at 07:03
  • I understand. Unfortunately I can't think of any better solution :-( Maybe somebody with more experience will help. – DangeMask May 26 '15 at 07:27
0

The things you like called pivot table, and this is out of scope of relation data base system like mySQL. So there many special platforms for OLAP. But if you do not need any comprehensive OLAP things, but produce some pivot tables using mySQL and PHP you do not need any special sql, but nedd to program such presentation by php, may be using some commercial frameworks like this or some opensource code

skazska
  • 421
  • 2
  • 8