title: Is it correct to join the same table multiple times on different conditions?
body: <p>I'm building a simple app based on a moodle (2.4) database. The db is mysql 5.1.69</p>
  
  <p>I need to find all certs granted this month and all relevant information about the user that got the cert and the cert itself.</p>
  
  <p>My sql as it stands is:</p>
  
  <pre><code>SELECT u.lastname, u.firstname, ln.data, lt.data, course.fullname,
      c.printhours, from_unixtime(i.timecreated) AS issued,
      substring_index(c.customtext, ' ', -1) AS approval
  FROM mdl_certificate_issues AS i
  INNER JOIN mdl_certificate AS c ON i.certificateid = c.id
  INNER JOIN mdl_user AS u on i.userid = u.id
  INNER JOIN mdl_course AS course ON c.course = course.id
  INNER JOIN mdl_user_info_data AS ln ON i.userid = ln.userid AND ln.fieldid = 1
  INNER JOIN mdl_user_info_data AS lt ON i.userid = lt.userid AND lt.fieldid = 2
  WHERE i.timecreated &gt;= unix_timestamp('2013-07-01')
      AND i.timecreated &lt; unix_timestamp('2013-08-01')
  </code></pre>
  
  <p>The fact that I'm joining the same table (mdl_user_info_data) twice (as lt and ln) and using the same columns, but differing only on the fieldid is what bothers me. It feels like I should be able to do that in a single subquery and pull the correct information into the select - but I just can't figure out how to do it, or if that is even the correct approach.</p>