title: Duplicate Column Name in a fairly complicated JOIN
body: <p>So I have a relatively complicated MySQL query that is giving a Duplicate Column, for reasons I haven't been able to determine. I'm somewhat new to SQL.</p>
  
  <p>The query is supposed to join a list of "problem" entries with the database of "tickets," specifically the latest ticket for the particular <code>id</code>. Thus I have an inner query that executes a <code>JOIN</code> of the <code>ticket</code> table on itself to get the latest ticket. This works correctly. I then attempt to <code>JOIN</code> this inner selection with the <code>problem</code> database, however, I get a Duplicate Column Name <code>id</code>, despite the fact that this is the column name I am joining on. My understanding is that when you join on a particular column, it shouldn't "count" as duplicate...</p>
  
  <p>Anyway, the query:</p>
  
  <pre><code>SELECT * FROM `problem` P
  LEFT JOIN (
      SELECT * FROM (
          SELECT id, MAX(ticket_id) AS ticket_id
          FROM `ticket`
          WHERE ticket_status = 'o'
          GROUP BY id
      ) AS MAX
      INNER JOIN (
          SELECT id, ticket_id, other_data, time_stamp FROM `ticket`
      ) CUR ON MAX.id = CUR.id AND MAX.ticket_id = CUR.ticket_id
  ) T ON P.id=T.id
  WHERE
          P.since IS NOT NULL;
  </code></pre>
  
  <p>As I said, if I do just this:</p>
  
  <pre><code>SELECT * FROM (
      SELECT id, MAX(ticket_id) AS ticket_id
      FROM `ticket`
      WHERE ticket_status = 'o'
      GROUP BY id
  ) AS MAX
  INNER JOIN (
      SELECT id, ticket_id, other_data, time_stamp FROM `ticket`
  ) CUR ON MAX.id = CUR.id AND MAX.ticket_id = CUR.ticket_id
  </code></pre>
  
  <p>I get a table of the latest ticket for each <code>id</code>.</p>
  
  <p>One thing I have tried to fix this is to <code>SELECT MAX.* FROM (</code> instead of just <code>SELECT * FROM (</code>. This eliminates the duplicate column name error, but the inner query no longer returns any <code>other_data</code> or <code>time_stamp</code> &ndash; I only get the <code>id</code> and <code>ticket_id</code> columns. This defeats the purpose of the join in the first place, of course.</p>