title: MySQL Inner Join with condition for the same table
body: <p>I have a table named <code>activities</code> which includes activities of different kinds, some of them are related. Each activity of type <code>A</code> is matched with an activity of type <code>B</code>, for simplicity let's assume there is no missing data. Up to this point the setup is very similar to <a href="https://stackoverflow.com/questions/14796738/inner-join-same-table/14796850">this question</a>.</p>
  <p>Each activity has a few columns, including <code>activity_type</code>, <code>related_activity_id</code> and <code>location_id</code>. I can query for the pairs very easily:</p>
  <pre><code>SELECT 
  A.location_id as 'A location id',
  B.location_id as 'B location id'
  FROM (SELECT * FROM activities WHERE `activities`.`activity_type` IN ('Activities::A') AND (`activities`.`related_activity_id` IS NOT NULL)) AS A
  INNER JOIN (SELECT * FROM activities WHERE `activities`.`activity_type` IN ('Activities::B') AND (`activities`.`related_activity_id` IS NOT NULL)) AS B
  ON A.related_act_id = B.id;
  </code></pre>
  <p>The problem is when I want to find the mismatching pairs, that is the pairs where the location of A does not match the location of B. I have tried using</p>
  <pre><code>WHERE A_location_id &lt;&gt; B_location_id
  </code></pre>
  <p>but then it does not recognize the columns:</p>
  <blockquote>
  <p>Error Code: 1054. Unknown column 'A_location_id' in 'where
  clause'   0.734 sec</p>
  </blockquote>
  <p>Unwillingly, I have converted the where clause to:</p>
  <pre><code>WHERE A.location_id &lt;&gt; B.location_id
  </code></pre>
  <p>But now the query is awfully long and MySQL workbench suffocates and the eventually kills the query:</p>
  <blockquote>
  <p>Error Code: 2013. Lost connection to MySQL server during query</p>
  </blockquote>
  <p>I have tried using a longer timeout query, it did not help even by a bit. Any ideas on how to tacke this?</p>