Search code examples
mysqlsqlgroup-concat

Mysql query only select rows with unique result within GROUP_CONCAT


Is there a way to select only the rows that have an other result than the row previous selected? In one of my tables I store advertisement data, that’s one row per advertisement. I also store in an other table the prices for rental per dag, week, month, this table contain more than one row per advertisement. I want to select al the rows from table 2 where there is a change in one of the prices (in the example row 1 and 3 in table 2) in the same query as the data selection. I know that I have to use a GROUP_CONCAT to get one row instead of a 2 row result in this case, but how to get 2 result rows from table 2 and 1 result row in total?

The outcome of the query has to be something like: tre,234,” 12345678911,12,45, 32555678911,12,67 ”

Table 1 (advertisements)
ID_adv      data1       data2   
1       tre     234
2       ghj     34
3       jk      098 
4       jfjk        12

Table 2 (dates)
ID_dates    ID_adv      timestamp_day   price1      price2
1       1       12345678911     12      45
2       1       22345677771     12      45
3       1       32555678911     12      67
4       2       42345671231     34      34

I tried

SELECT 
t1.*, 
GROUP_CONCAT(t2.date) AS dates 
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.ID_adv = t1.ID_adv 
WHERE t1.ID_adv = 3 GROUP BY t1.ID_adv

Solution

  • Can you try this one:

    SELECT T3.ID_adv
        , T3.data1
        , T3.data2
        , CAST(GROUP_CONCAT(CONCAT(T3.timestamp_day, ',', T3.price1, ',', T3.price2)) AS CHAR) AS DatePrice
        FROM (
            SELECT T1.*
                , MIN(T2.timestamp_day) AS timestamp_day
                , T2.price1
                , T2.price2
                FROM Table1 T1
                LEFT JOIN Table2 T2 ON T2.ID_adv = T1.ID_adv
                GROUP BY T1.ID_adv, T2.price1, T2.price2
        ) T3
        GROUP BY T3.ID_adv;
    

    I've tried it on SQL Fiddle.