title: substitute one value with another from a different table in SELECT statement
body: <p>I'm trying come up with a SQL statement to print all the duplicate [exported-resource] definition in the Puppet database.</p>
  
  <pre><code>mysql&gt; SELECT id,restype,host_id,source_file_id FROM resources    
      -&gt; WHERE title IN (SELECT title FROM resources WHERE exported=1 
      -&gt; GROUP BY title HAVING count(title) &gt; 1) ORDER BY title;
  +------+------------------------+---------+----------------+
  | id   | restype                | host_id | source_file_id |
  +------+------------------------+---------+----------------+
  |  305 | Nagios::Client::Export |       2 |             18 |
  |  333 | Nagios_host            |       2 |             39 |
  |  605 | Nagios_hostextinfo     |       6 |              2 |
  |  443 | Nagios_hostextinfo     |       2 |             39 |
  |  499 | Nagios_host            |       6 |              2 |
  |  770 | Nagios::Client::Export |       6 |             18 |
  ......
  ......
  </code></pre>
  
  <p>Which is working just fine, but how can I retrieve/print <em>hosts.name</em> from <em>hosts</em> table in stead of the <code>host_id</code>. I just can't get my head around with rewriting the above SQL statement. The <em>hosts</em> table looks like this:</p>
  
  <pre><code>mysql&gt; SELECT id,name  FROM hosts;
  +----+-----------------------------------------+
  | id | name                                    |
  +----+-----------------------------------------+
  |  2 | controller-dns-01.sdas.cloud.com        |
  |  6 | controller-monitoring-01.sdas.cloud.com |
  |  1 | controller-puppet.sdas.cloud.com        |
  | 13 | talend-admin-01.sdas.cloud.com          |
  | 15 | talend-jobserver-01.sdas.cloud.com      |
  | 14 | talend-jobserver-02.sdas.cloud.com      |
  +----+-----------------------------------------+
  </code></pre>
  
  <p>Also, is there a way to print only the first part of the <em>hostname</em> (i.e. only <code>controller-dns-01</code>) in stead of the complete string? Any suggestion from any one greatly appreciated. Cheers!!</p>
  
  <p><hr>
  <strong>Update:</strong><br>
  This is my final command: Just in case if someone else also looking for a way to find out the Puppet Exported resources duplicate definitions</p>
  
  <pre><code>mysql&gt; CREATE INDEX index_resources_on_restypetitle ON resources (restype(12),title(12));
  mysql&gt; SELECT r.id, r.restype, r.title, SUBSTRING_INDEX(h.name,'.',1) AS 'host_name',
      -&gt; SUBSTRING_INDEX(s.filename,'puppet/',-1) AS 'file_name', r.line FROM resources r 
      -&gt; LEFT JOIN hosts h ON r.host_id = h.id LEFT JOIN source_files s ON r.source_file_id = s.id 
      -&gt; WHERE MD5(CONCAT(restype,title,host_id)) 
      -&gt; IN (SELECT MD5(CONCAT(restype,title,host_id)) FROM resources 
      -&gt; WHERE exported=1 GROUP BY MD5(CONCAT(restype,title,host_id)) 
      -&gt; HAVING COUNT(MD5(CONCAT(restype,title,host_id))) &gt; 1) ORDER BY title;
  </code></pre>
  
  <p>the <code>SUBSTRING_INDEX(s.filename....)</code> bit may needs readjusting according to the configuration. A big thank to thiella for helping me out.</p>