0

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...

Improve this question
8
  • Are there only 4 ad types? if there's no data for an ad type, what should be in the second column: nothing (empty string), duration : 0 common : 0, or something else?
    – RDFozz
    May 17, 2017 at 16:56
  • yes they are 4 ; there has to be data in ad_type ...
    – Garvit Jain
    May 18, 2017 at 5:01
  • Let me rephrase: in your first example, what values should be in the second column for "center", "left", and "right1:?
    – RDFozz
    May 18, 2017 at 14:28
  • The actual query is select concat(ad_type,'Ad') as c1,GROUP_CONCAT( concat('<div class="',ad_type,'Ad" alt=',duration,'><a href="',ad_link,'" target="_blank"><img src="',img_location,'" width="100%" height="100px"/><span class="w3-small w3-text-grey w3-left">',ad_title,'<br/></span></a></div>') ORDER BY sorter ASC SEPARATOR '') as c2 which I thought would waste readers time so I simplified it. Previously I was doing it in the program code i.e. select statement in a loop which I read is bad thing to do.
    – Garvit Jain
    May 19, 2017 at 6:38
  • It would still be helpful to see what results you want to be returned from your first data set.
    – RDFozz
    May 19, 2017 at 15:43

2 Answers 2

Reset to default
1

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

  1. 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'.
  2. 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.
  3. 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.
  4. 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.

Improve this answer
5
  • Thank you so much for your time; The problem is still the third assumption which is what the problem was before; The IF EXIST should test each distinct ad_type and then return the condition i.e. ad_timeout >= CURDATE() Also page is foreign key or blank or a word or a null . I've edited the question with another query which I'm currently using. Is there a better approach?
    – Garvit Jain
    May 19, 2017 at 6:33
  • Yes, and that's what it's doing. If we find a page with the ID you specify, we return that page and the ad rows for any of the four ad_types we do have a row for, plus a rows with all ad values set to NULL where no such row exists; if there is no page whatsoever with the desired ID, we return the same as above for all pages that haven't expired.
    – RDFozz
    May 19, 2017 at 15:47
  • Unless you mean you want your check done explicitly for each page and ad_type: If there's a page 1234 with ad types "head" and "center", then return the info for page 1234 for "head" and "center"; for "right1" and "left" return the info for all unexpired pages? Please clarify
    – RDFozz
    May 19, 2017 at 15:50
  • I supply a page I want if exist to check where page = 'blah' where ad_type= 'center' if not then return other condition... Then do the same for next ad_type kind of union like in my question
    – Garvit Jain
    May 20, 2017 at 16:49
  • Updated based on your last comment, which clarified the desired results.
    – RDFozz
    May 21, 2017 at 20:56
1

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 >= 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 >= 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 )
Improve this answer
1
  • Yes I'm doing this however, what I want is that the IF EXIST part executes for each group ie. page='1494573224714' and ad_type='head' , page='1494573224714' and ad_type='center' because IF EXIST may be satisfied for ad_type=head and not ad_type=center But I want result for each group!
    – Garvit Jain
    May 18, 2017 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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