Q: MySQL Subselect Issue with two tables and aggregate functions into single query I have 2 tables 
Transaction table
    +----+----------+-----+---------+----
    | TID | CampaignID    | DATE        | 
    +----+----------+-----+---------+---+
    |  1 |              5 | 2016-01-01  |  
    |  2 |              5 | 2016-01-01  |
    |  3 |              2 | 2016-01-01  |  
    |  4 |              5 | 2016-01-01  |  
    |  5 |              1 | 2016-01-01  | 
    |  6 |              1 | 2016-02-02  |
    |  7 |              3 | 2016-02-02  | 
    |  8 |              3 | 2016-02-02  |
    |  9 |              5 | 2016-02-02  | 
    |  10|              4 | 2016-02-02  | 
    +----+----------+-----+---------+---+

Campaign Table
    +-------------+----------------+--------------------
    | CampaignID | DailyMaxImpressions  | CampaignActive 
    +-------------+----------------+--------------------
    |           1 |              5 |                Y  |  
    |           2 |              5 |                Y  |
    |           3 |              5 |                Y  |  
    |           4 |              5 |                Y  |  
    |           5 |              1 |                Y  | 
    +-------------+----------------+--------------------

What I am trying to do is get a single random campaign where the the count in transaction table is less than the daily max impressions in the campaign table. I might also be passing a date s part of the query for the transaction table
So for CampaignId 1 there must be 4 trans of less in the transaction table and the Campaignactive must be a "Y"
Any help would be appreciated if this can be done in a single statement. ( mysql )
Thanks in advance,
Jeff Godstein

A: This should get it for you.  The basic query is select each campaign that is active.  The INNER query will pre-aggregate per campaign for the given date in question.  From that, a LEFT-JOIN allows any campaign to be returned even if it does NOT exist within the subquery OR it DOES exist, but the count is less than that allowed for the date in question.  The order by RAND() is obvious.
SELECT
      c.CampaignID
   from
      Campaign c
         LEFT JOIN 
         ( select 
                 t1.CampaignID,
                 count(*) as CampCount
              from
                 Transaction t1
              where
                 t1.Date = YourDateParameterValue 
              group by
                 t1.CampaignID ) as T
            ON c.CampaignID = T.CampaignID 
   where
          c.CampaignActive = 'Y'
      AND ( t.CampaignID IS NULL
         OR t.CampCount < c.DailyMaxImpressions )
   order by
      RAND()

