Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following table,

id | booki_id | bet_id   |      bet  | bettype | line | odds 
1  |   123    |   321    |        1  |    3way |  0,0 |  2.3
2  |   123    |   321    |        2  |    3way |  0,0 |  3.4
3  |   123    |   322    |        1  |    3way |  0,0 |  1.1
4  |   123    |   322    |        2  |    3way |  0,0 |  7.4
5  |   123    |   323    |        1  |    3way |  0,0 |  1.3
6  |   123    |   323    |        2  |    3way |  0,0 |  9.4

How can I get the following array? Or can I get wit one selection?

array
(
[0]=>array
(
   "booki_id"=>123,
   "betid"=>321,
   "bet"=>array([1]=>2.3
                [2]=>3.4
                ),
    "bettype"=>"3way",
    "line"=>0
),
[1]=>array
(
   "booki_id"=>123,
   "betid"=>323,
   "bet"=>array([1]=>1.3
                [2]=>9.4
                ),
    "bettype"=>"3way",
    "line"=>0
)
[2]=>array
(
   "booki_id"=>123,
   "betid"=>322,
   "bet"=>array([1]=>1.1
                [2]=>7.4
                ),
    "bettype"=>"3way",
    "line"=>0
)
)

And It's ordered by bet[1];

UPDATED:

id | booki_id | bet_id   |      bet  | bettype | line | odds 
1  |   123    |   321    |        1  |    3way |  0,0 |  2.3
2  |   123    |   321    |        2  |    3way |  0,0 |  3.4
3  |   123    |   321    |        x  |    3way |  0,0 |  2.4
4  |   123    |   322    |        1  |    3way |  0,0 |  1.1
5  |   123    |   322    |        2  |    3way |  0,0 |  7.4
6  |   123    |   322    |        x  |    3way |  0,0 |  3.4
7  |   123    |   323    |        1  |    3way |  0,0 |  1.3
8  |   123    |   323    |        2  |    3way |  0,0 |  9.4
9  |   123    |   323    |        x  |    3way |  0,0 |  6.4

And sometimes I have to order by bet[2] or bet[x];

share|improve this question
    
I'm not sure I understand properly, actually you want a SELECT statement to return to you rows in the following format: bookid, betid, bet (concatenated bets), bettype and line? Thanks. –  Marian Jul 21 '11 at 22:25
    
Yes, I want to concatenate the odds where the bet_id equal . –  run Jul 22 '11 at 6:19
2  
The following function is your saver: group_concat function. You can see how to use it in this SO question. –  Marian Jul 22 '11 at 8:16
add comment

1 Answer

up vote 2 down vote accepted

I tried to produce the same ordering and having some trouble but here are two different queries you can start with:

SELECT
    booki_id,bet_id,bettype,
    group_concat(odds order by bet) betodds,line
FROM
    bettable
GROUP BY
    booki_id,bet_id,bettype,line
;

SELECT * FROM
(
    SELECT
        booki_id,bet_id,bettype,
        group_concat(odds order by bet) betodds,line
    FROM
        bettable
    GROUP BY
        booki_id,bet_id,bettype,line
) AA
ORDER BY betodds
;

I also created sample data to test it with, and here it is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use test
Database changed
MySQL (Current test) :: DROP TABLE IF EXISTS bettable;
Query OK, 0 rows affected (0.03 sec)

MySQL (Current test) :: CREATE TABLE bettable
    -> (
    ->     id       INT NOT NULL AUTO_INCREMENT,
    ->     booki_id INT,
    ->     bet_id   INT,
    ->     bet      INT,
    ->     bettype  VARCHAR(10),
    ->     line     FLOAT,
    ->     odds     FLOAT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

MySQL (Current test) :: INSERT INTO bettable (booki_id,bet_id,bet,bettype,line,odds) VALUES
    -> (123,321,1,'3way',0.0,2.3),
    -> (123,321,2,'3way',0.0,3.4),
    -> (123,322,1,'3way',0.0,1.1),
    -> (123,322,2,'3way',0.0,7.4),
    -> (123,323,1,'3way',0.0,1.3),
    -> (123,323,2,'3way',0.0,9.4);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL (Current test) :: SELECT
    ->     booki_id,bet_id,bettype,
    ->     group_concat(odds order by bet) betodds,line
    -> FROM
    ->     bettable
    -> GROUP BY
    ->     booki_id,bet_id,bettype,line
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.01 sec)

MySQL (Current test) :: SELECT * FROM
    -> (
    ->     SELECT
    ->         booki_id,bet_id,bettype,
    ->         group_concat(odds order by bet) betodds,line
    ->     FROM
    ->         bettable
    ->     GROUP BY
    ->         booki_id,bet_id,bettype,line
    -> ) AA
    -> ORDER BY betodds
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.02 sec)

MySQL (Current test) ::

You will have to play with the order of the whole rows from here.

Give it a Try !!!

share|improve this answer
    
It is a really nice solution and complex, but I updated my table, and the ordering is depending sometimes I have to order where the bet is 1, you solved this, but sometimes I have to order where the bet is 1 or x –  run Jul 22 '11 at 16:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.