MySql query to concat 2 rows of the same column

Question

Date table:

id  start_date  end_date
1   2013-04-28  2013-04-29
3   2013-04-18  2013-04-25
4   2013-05-22  2013-04-30
5   2013-05-02  2013-04-30
6   2013-04-29  2013-04-30

Time Table:

id  start_time  end_time
1   11:00 AM    12:00 AM
2   12:00 PM    03:00 PM

App table:

id  app_name
1   Test
2   Test1

Relation of the table is store in related table:

app_id  date_id  time_id
1          1       1 
1          1       2 
2          1       2

I firing a sql query to get the data from database :

SELECT app.id as id,app.app_name,date.id as date_id ,
                date.start_date,date.end_date,time.id as time_id,time.start_time,time.end_time as end_time
                FROM  related_data
                INNER JOIN app ON app.id = app_id
                INNER JOIN DATE ON date.id = date_id
                INNER JOIN Time ON time.id = time_id
                LIMIT 0 , 30

but it return :

id  app_name    date_id     start_date  end_date    time_id     start_time  end_time
1   Test    1   2013-04-28  2013-04-29  1   11:00 AM    12:00 AM
1   Test    1   2013-04-28  2013-04-29  2   12:00 PM    03:00 PM
2   Test1   1   2013-04-28  2013-04-29  2   12:00 PM    03:00 PM

but I want like this:

id  app_name    date_id     start_date  end_date    time_id     start_time  end_time
1   Test    1   2013-04-28  2013-04-29  1,2     11:00 AM,12:00PM    12:00 AM,03:00PM
2   Test1   1   2013-04-28  2013-04-29  2   12:00 PM    03:00 PM

If app id is same and date id or time id different then concat the data.

John Woo · Accepted Answer · 2013-04-30 05:52:59Z

1

use GROUP_CONCAT

SELECT  app.id as id,
        app.app_name,
        date.id as date_id , 
        date.start_date,
        date.end_date,
        GROUP_CONCAT(time.id) as time_id,
        GROUP_CONCAT(time.start_time) start_time,
        GROUP_CONCAT(time.end_time) as end_time 
FROM    related_data 
        INNER JOIN app 
            ON app.id = app_id 
        INNER JOIN DATE 
            ON date.id = date_id 
        INNER JOIN Time 
            ON time.id = time_id 
GROUP   BY  app.id as id,
            app.app_name,
            date.id as date_id , 
            date.start_date,
            date.end_date
LIMIT   0, 30

MySQL GROUP_CONCAT()

answered Apr 30, 2013 at 5:52

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Over a year ago

Can we concat the start_time & end_time with in a single column ?

John Woo Over a year ago

try, CONCAT(start_date, ' to ', end_date)

John Over a year ago

Can i do it.. if time_id is not same then concat the date otherwise only one data..?

John Woo Over a year ago

add DISTINCT example, GROUP_CONCAT(DISTINCT time.id)

Collectives™ on Stack Overflow

MySql query to concat 2 rows of the same column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related