Mysql – IF EXISTS on each group in Mysql

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…

Best Answer

OK, I'm going to make a few assumptions here:

The primary key for ad is page. If that's not the primary key, then the pxad subquery below should have the primary key column(s) instead of 'page'.
There are only four possible values for ad_type (center, left, head, right1), period. The alternative would be some pages might have (center, head, left, right1) and others might have (head, footer, right1, popup), etc.; that would require additional info to manage.
If a given row in ad has no entries for a particular ad_type (for example, there are rows for page = '1234567' with ad_type "head" and "left", but not "right1" or "center"), then we should return all unexpired pages for that ad type.
The desired results for your first query should be the one row you show, plus the the rows from your second result set for "center", "left", and "right1".

Given the above is accurate, I'd try something along the lines of this (based on the first query you provided:

SELECT pxad.ad_type
      ,GROUP_CONCAT( concat(' duration : ',ad.duration,' common :',ad.common)
                     ORDER BY ad.sorter ASC SEPARATOR ' , '
                   ) 

  FROM (SELECT ad1.duration
              ,ad1.common
              ,ad1.sorter
              ,ad1.ad_type
          FROM ad ad1
                 CROSS JOIN (SELECT 'center' as ad_type UNION ALL
                             SELECT 'head' UNION ALL
                             SELECT 'left' UNION ALL
                             SELECT 'right1'
                            ) as AdType
         WHERE IF( EXISTS (SELECT 1 FROM ad WHERE page = '1494573224714' and ad.ad_type = AdType.ad_type)
                  ,page = '1494573224714'
                  ,ad_timeout >= CURDATE() 
                 )
           AND ad1.ad_type = AdType.ad_type
       ) pxad
 GROUP BY ad_type;

(NOTE: code is untested)

The subquery labeled pxad will check for records with your desired page value, and each of the four possible ad_types. Where it finds matches, it uses those; where it doesn't, it returns all unexpired pages for the ad_type.

Related Solutions

MySql: #1221 – Incorrect usage of UNION and ORDER BY

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM     
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp 
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.* 
FROM comment AS tbl 
    JOIN 
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION 
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp 
      ON tmp.sscm_id = tbl.sscm_id ;

Mysql – Unable to get the latest value added to Mysql table based on posted time

Your first HUGE mistake (as @ypercube pointed out) is not using a DATETIME type for a date-time type variable. TIMEs are not VARCHARs and doing this will mess up your queries, make your app non-portable and will confuse the optimiser.

The other HUGE problem is MySQL. It doesn't perform this sort of query properly. Your query is ambiguous - PostgreSQL for example will throw an error if you try and run such a query.

For example

mysql> SELECT id, MAX(posted_date) FROM example GROUP BY id;
+------+------------------+
| id   | MAX(posted_date) |
+------+------------------+
|    1 | 2015-03-27       |
|    2 | 2015-03-15       |
+------+------------------+
2 rows in set (0.00 sec)

mysql>

is unambiguous and gives the correct answer.But, because you have tried to use a SELECT * FROM example, the MySQL engine doesn't know which values to return and (stupidly enough) appears (at least in the several examples of this problem that I have seen) to return the correct unambiguous values, but the other are random - so you could get id = 1 (correct) with cname = 'ghi' (incorrect).

Check out MySQL's SQL modes - there's one (ONLY_FULL_GROUP_BY) that disallows this behaviour. It should be switched on at ALL times.

To get your query to work properly, for starters, use a DATETIME type (makes the subquery below easier) and then do a subquery for cname where your DATETIME value matches that for the correct cname. HTH,

[EDIT]

Using this schema and data, your query becomes relatively easy.

CREATE TABLE example
(
id int(11),
cname varchar(10),
posted_datetime datetime
); 


INSERT INTO example
VALUES (1, 'abc', '2015-03-26 04:25');

INSERT INTO example
VALUES (1, 'def', '2015-03-27 16:30');

INSERT INTO example
VALUES (2, 'ghi', '2015-03-11 02:25');

INSERT INTO example
VALUES (2, 'jkl', '2015-03-15 12:25');

And your working query is:

SELECT e.id, e.cname, e.posted_datetime 
FROM example e
  JOIN
  (
    SELECT id, MAX(posted_datetime) AS posted_datetime
    FROM example 
    GROUP BY id
  ) m
  ON  m.id = e.id 
  AND m.posted_datetime = e.posted_datetime ;

or check it out here: SQLfiddle

Best Answer

Related Solutions

MySql: #1221 – Incorrect usage of UNION and ORDER BY

Mysql – Unable to get the latest value added to Mysql table based on posted time

Related Question