title: Subquery in Join in CodeIgnitier
body: <p>i have following mysql query</p>
  
  <pre><code>SELECT
    events.id      AS id,
    A.sold_tickets
  FROM (`events`)
    JOIN `category` AS cat
      ON `events`.`category_id` = `cat`.`id`
    JOIN `category` AS sub_cat
      ON `events`.`subCategoryID` = `sub_cat`.`id`
    JOIN `events_custom_dates` AS events_date
      ON `events_date`.`event_id` = `events`.`id`
    JOIN `my_promos`
      ON `events`.id = `my_promos`.`event_id`
    LEFT JOIN `mycalendar`
      ON `mycalendar`.`event_id` = `my_promos`.`event_id`
    LEFT JOIN `promo_events_stats`
      ON `promo_events_stats`.`id` = `events`.`id`
    LEFT JOIN (SELECT
             my_promos.event_id,
             SUM(tickets_sold.quantity) AS sold_tickets
           FROM my_promos
             JOIN tickets_sold
           ON tickets_sold.code = my_promos.link_code
           WHERE my_promos.user_id = '532'
           AND DATE(my_promos.date) &gt;= '2013-11-01'
           AND DATE(my_promos.date) &lt;= '2014-01-22'
           GROUP BY my_promos.event_id) A
      ON A.event_id = events.id
  WHERE `my_promos`.`user_id` = '532'
      AND DATE(my_promos.date) &gt;= '2013-11-01'
      AND DATE(my_promos.date) &lt;= '2014-01-22'
  GROUP BY my_promos.event_id
  </code></pre>
  
  <p>i want to convert the join part containing the subquery into codeignitier way. that is following </p>
  
  <pre><code>LEFT JOIN (SELECT
         my_promos.event_id,
         SUM(tickets_sold.quantity) AS sold_tickets
       FROM my_promos
         JOIN tickets_sold
       ON tickets_sold.code = my_promos.link_code
       WHERE my_promos.user_id = '532'
       AND DATE(my_promos.date) &gt;= '2013-11-01'
       AND DATE(my_promos.date) &lt;= '2014-01-22'
       GROUP BY my_promos.event_id) A
  ON A.event_id = events.id
  </code></pre>
  
  <p>i want this above in codeignitier query structure</p>