2

I have 3 tables. I need to join 2 of them and have the join pivot the joined table to return the values in the row. Read below for clarification.

Table 1:
t1ID   Title
01     Title 1
01_01  Title 1a
01_02  Title 1b
01_03  Title 1c
02     Title 2
02_01  Title 2a
02_02  Title 2b

... and so on

Table 2 (not used in my join, but shown to see relationship between 3 tables -- contains a fixed number of rows which is 10):
t2ID  Description
01    Desc A
02    Desc B
03    Desc C
...
10    Desc J


Table 3:
t1ID   t2ID  Value
01     01    A
01     02    B
...
01     10    C
01_01  01    D
01_01  02    E
...
02_01  10    F
02_02  01    G
02_02  02    H

...and so on

I want to join Table 1 and Table 3 on t1ID where Table 1 is the main or master and Table 3 provides detailed values. I need to order Table 3 on t2ID to keep the sequence so I can handle the output properly.

I would like the query results to look like this:

row[0]    row[1]  row[2] ... row[10]
Title 1   A       B          C
Title 1a  D       E

So I need each row to have the 1st item in the output array as the title and then items 2 through 11 of the output array coming from the join Table 3.

Hope that makes sense.

Thanks for helping me on this.

AMENDED (Getting closer, or at least I no longer have the derived alias error message):

SELECT t1.t1ID, t1.Title, GROUP_CONCAT(t3.Value) AS value
FROM Table1 AS t1
JOIN Table3 AS t3 ON t3.t1ID = t1.t1ID
GROUP BY t3.t1ID
ORDER BY t1.t1ID

All I need to do now is have Table 3 sorted in t2ID ORDER

Can anyone assist with that?

H. Ferrence
  • 7,906
  • 31
  • 98
  • 161

2 Answers2

2

You can sort group_concat() output as follows:

SELECT
    t1.t1ID,
    t1.Title,
    GROUP_CONCAT(t3.Value order by t2ID) AS value
FROM Table1 AS t1
JOIN Table3 AS t3 ON t3.t1ID = t1.t1ID
GROUP BY t3.t1ID
ORDER BY t1.t1ID

Notice the order by within the group_concat() call.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

You can't do that easily with MySQL, but the GROUP_CONCAT function will probably work for you. The query would look like this:

SELECT Title, GROUP_CONCAT(Value) AS values
FROM Table1 AS t1
    JOIN (
        SELECT t1ID, Value FROM Table3 ORDER BY t2ID ASC
    ) AS t2 USING(t1ID)
GROUP BY t1ID

The result of this query will be something like

Title    | values
---------|-------
Title 1  | A,B,C
Title 1a | D,E

You should then be able to explode the string of values into an array. If you absolutely have to actually pivot the data, this question has more helpful information.

Edit: Bohemian is correct and his method is better than mine since it uses a JOIN instead of a sub-query.

Community
  • 1
  • 1
G-Nugget
  • 8,666
  • 1
  • 24
  • 31
  • Thanks G-Nugget,exploding will work for what I need to so. I will try your solution and post back. Thanks again – H. Ferrence Feb 21 '13 at 22:53
  • I am testing now -- I got the following error message `Every derived table must have its own alias` @G-Nugget ... I am looking into it – H. Ferrence Feb 22 '13 at 13:19
  • I have added an `AS ...` to both `FROM` statements and I still get the error message `Every derived table must have its own alias` – H. Ferrence Feb 22 '13 at 13:32
  • I have amended my initial question with some code I am getting to work. I just need to have a sort order on Table 3. Thanks @G-Nugget – H. Ferrence Feb 22 '13 at 14:30
  • @H.Ferrence Yeah, that error you got is true. I'm updating my question with aliases for the tables that should fix the error. Adding the second table as a sub-query is not optimal, but is required since the data on it has to be ordered before it gets joined. – G-Nugget Feb 22 '13 at 14:40