IF EXISTS on each group in Mysql? Sample Data :


| ad_id | ad_type | duration |          page | common | sorter |
|-------|---------|----------|---------------|--------|--------|
|    20 |  center |        2 |               |  false |      0 |
|    21 |    left |        5 |               |  false |      0 |
|    19 |    left |        1 |               |  false |      0 |
|    18 |    head |        2 |               |   true |      0 |
|    17 |  right1 |        4 |               |   true |      1 |
|    16 |  right1 |        5 |               |  false |      0 |
|    22 |  center |        3 |               |   true |      0 |
|    23 |    head |        6 |               |   true |      0 |
|    25 |    head |        2 |               |  false |      0 |
|    29 |    left |        5 |               |  false |      0 |
|    30 |    left |        7 |               |  false |      0 |
|    32 |    head |        4 | 1494573224714 |  false |      2 |
|    33 |    left |        5 | 1494573224714 |  false |      2 |
|    34 |    left |        1 | 1494573224714 |  false |      2 |
|    35 |    left |        9 | 1494066642553 |  false |      1 |
|    36 |    left |       10 | 1494066642553 |  false |      3 |
|    38 |    head |        5 | 1494066642553 |  false |      1 |


and desired output :


| ad_type | GROUP_CONCAT( duration ORDER BY sorter ASC SEPARATOR ' , ') |
|---------|-------------------------------------------------------------|
|    head |                                                           4 |
|  center |                                                           3 |
|  right1 |                                                           4 |
|    left |                                                       1 , 5 |


http://sqlfiddle.com/#!9/e67161/1

I'm using the following query:


select ad_type,
GROUP_CONCAT( duration ORDER BY sorter ASC SEPARATOR ' , ') 
from ad 

WHERE ad_type='head' AND
 IF( EXISTS 
    (SELECT 1 FROM ad where page='1494573224714' AND ad_type='head')
     , page='1494573224714', common=1  )
group by ad_type

UNION
select ad_type,
GROUP_CONCAT( duration ORDER BY sorter ASC SEPARATOR ' , ') 
from ad 

WHERE ad_type='center' AND
 IF( EXISTS 
    (SELECT 1 FROM ad where page='1494573224714' AND ad_type='center')
     , page='1494573224714', common=1  )
group by ad_type


UNION
select ad_type,
GROUP_CONCAT( duration ORDER BY sorter ASC SEPARATOR ' , ') 
from ad 

WHERE ad_type='left' AND
 IF( EXISTS 
    (SELECT 1 FROM ad where page='1494573224714' AND ad_type='left')
     , page='1494573224714', common=1  )
group by ad_type


Is there a way something like IN('head','left','right1','center') 

or to perform on all distinct ad_type?

The Query I'd used that failed :


select ad_type,
GROUP_CONCAT( duration ORDER BY sorter ASC SEPARATOR ' , ') 
from ad 
WHERE 
 IF( EXISTS 
    (SELECT 1 FROM ad where page='1494573224714')
     , page='1494573224714', common=1  )
group by ad_type



Which returns :


| ad_type | GROUP_CONCAT( duration ORDER BY sorter ASC SEPARATOR ' , ') |
|---------|-------------------------------------------------------------|
|    head |                                                           4 |
|    left |                                                       1 , 5 |


I want the condition to get applied on each group so that I get results with 'center' ad_type as common=1 etc...
I suspect you are doing this:

select  Outer.ad_type,
GROUP_CONCAT( concat(' duration : ',duration, ' common :',common)
            ORDER BY  sorter ASC SEPARATOR ' , '
            )
    from  ad AS  Outer
    WHERE  IF( EXISTS ( SELECT  1 FROM  ad AS Inner
                    where  Inner.page='1494573224714' ) ,
                           Inner.page='1494573224714',
                           Inner.ad_timeout &gt;= CURDATE() 
             )
    group by  Outer.ad_type;


Try it; it should give you an error.

You are really doing

I suspect you are doing this:

select  Outer.ad_type,
GROUP_CONCAT( concat(' duration : ',duration, ' common :',common)
            ORDER BY  sorter ASC SEPARATOR ' , '
            )
    from  ad AS  Outer
    WHERE  IF( EXISTS ( SELECT  1 FROM  ad AS Inner
                    where  Inner.page='1494573224714' ) ,
                           Outer.page='1494573224714',
                           Outer.ad_timeout &gt;= CURDATE() 
             )
    group by  Outer.ad_type;


This explains your question??

More

Here might be the first step in getting rid of the UNIONs:

FROM ad AS ad1
JOIN ad AS ad2  ON ad2.page    = ad1.page
               AND ad2.ad_type = ad2.ad_type)  -- left/right/...
WHERE ( ad2.page='1494573224714'
     OR ad1.common )