1

I have 2 tables and I am trying to output the most efficient table JOIN on a mySQL query.

Table 1: qa_framework_activities

id | activity
+--|-------------------
 1 | text 
 2 | more text
 3 | even more text
 4 | more andmore text
 5 | blah blah
 6 | blah blah

and......

Table 2: qa_framework_indicators

 id | pid | indicators                    | chrono
+---|-----+-------------------------------+--------
 1  |  2  | text blah blayh               | 1
 2  |  2  | more text blah                | 4
 3  |  3  | even more text lipsum         | 3  
 4  |  6  | more andmore text lipsum blah | 6

pid = parent id (the id in the first table)

So the presumed mySQL query would be:

SELECT 
    qa_framework_activities.id, 
    qa_framework_indicators.id 
FROM 
    qa_framework_activities, 
    qa_framework_indicators 
WHERE 
    qa_framework_activities.id = qa_framework_indicators.pid

The issue I am having is how do I most efficiently display the results (qa_framework_indicators.indicators grouped by qa_framework_activities.activity) sorted by Chrono value in the Chrono column to show in the below table -- any one of 6 columns?

I want to output the above data based on the chrono table 2 column (chrono value is 1 - 6)

So the end result would look like:

 Chrono 1  | Chrono 2    | Chrono 3    | Chrono 4    | Chrono 5    | Chrono 6    |
+----------|-------------|-------------|-------------|-------------|-------------|
 Activity  | Activity    | Activity    | Activity    | Activity    | Activity    |
 indicator | indicator   | indicator   | indicator   | indicator   | indicator   |
 indicator |             |             |             |             |             |
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
mandalorianwarrior
  • 147
  • 16
  • 4
    When one says "most efficient way" it means that they already have a solution that *works* but not fast enough (or consumes more memory than it is available). Do you have any solution that already work? – zerkms Jul 18 '12 at 03:26
  • 2
    sounds(reads) like the query is fine, its the process of the output at issue? –  Jul 18 '12 at 03:32
  • 1
    Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and `INSERT ... VALUES` for samples. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. [Implicit joins](http://stackoverflow.com/questions/44917/) shouldn't be used in favor of [explicit joins](http://stackoverflow.com/questions/2241991/). – outis Jul 18 '12 at 07:27
  • 1
    Apart from the query challenge we need to know where you want to output this. Formatting like this is easy to do in an html page for example, so yuo could just return the correct data to the page skipping the query complexity inherent to the display you wish to have. For your information I'm pretty sure mysql won't allow you to do that. – Sebas Jul 18 '12 at 13:34
  • Dagon, The second part is he difficult part... Actually outputting the information to the chrono table... What is the most efficient and least resource intensive way to do this? Can someone guest sample code please? Thanks – mandalorianwarrior Jul 18 '12 at 14:16

0 Answers0