title: Select Count of Rows with Joined Tables
body: <p>I have two tables with a one to many relationship. I join the tables by an id column. My problem is that I need a count of all matching entries from the second (tablekey_id) table but I need the information from the row marked with the boolean <code>is_basedomain</code>. As a note there is only one row with <code>is_basedomain</code> = 1 per set of rows with the same tablekey_id. </p>
  
  <p>Table: tablekey</p>
  
  <pre><code>id  linkdata_id  timestamp
  22  9495028175   2013-03-10 01:13:46
  23  8392740179   2013-03-10 21:23:25
  </code></pre>
  
  <p>Table: searched_domains.</p>
  
  <p>NOTE: tablekey_id is the foreign key to the id in the tablekey table.</p>
  
  <pre><code>id  tablekey_id  domain        is_basedomain
  1   22           somesite.com  1
  2   22           yahoo.com     0
  3   23           red.com       1
  4   23           blue.com      0
  5   23           green.com     0
  </code></pre>
  
  <p>Heres the query Im working with. I was trying to use a sub query but I cant seem to select only the count for the current tablekey_id so this does not work.</p>
  
  <pre><code>SELECT `tablekey_id`, `linkdata_id`, `timestamp`, `domain`, `is_basedomain`,
     (SELECT COUNT(1) AS other FROM `searched_domains` AS dd 
      ON dd.tablekey_id = d.tablekey_id GROUP BY `tablekey_id`) AS count
  FROM `tablekey` AS k 
  JOIN `searched_domains` AS d
  ON k.id = d.tablekey_id 
  WHERE `is_basedomain` = 1 GROUP BY `tablekey_id`
  </code></pre>
  
  <p>The result that I would like to get back is:</p>
  
  <pre><code>tablekey_id  linkdata_id  timestamp            domain        is_basedomain    count
  22           9495028175   2013-03-10 01:13:46  somesite.com  1                2
  23           8392740179   2013-03-10 21:23:25  red.com       1                3
  </code></pre>
  
  <p>Can anyone help me get this into one query?</p>