title: SQLite Person Involved in A OR B
body: <p>I'll be honest this is a homework question, but I've wasted hours into it and just can't get it right. It returns either the wrong number of results or the wrong data:</p>
  
  <p>I need to select every person involved in directing a movie and/or acting in a movie and the amount of times they do, if it's at least 5 times.</p>
  
  <p>There is an <code>acts</code> table with a <code>movie_id</code> and a <code>person_id</code>, <code>directs</code> with <code>movie_id</code> and <code>person_id</code> and <code>person</code> (<code>pid</code> and <code>name</code>).</p>
  
  <p>What i've come up with is:</p>
  
  <pre><code> SELECT p.name,count(d.mid)+count(a.mid) 
      FROM person p
      INNER JOIN  acts a
      ON a.pid=p.pid
  
      LEFT JOIN  directs d
      ON p.pid=d.pid
  
      group by p.pid,p.name
  
      having (count(d.mid) +count(a.mid))&gt;=5
  </code></pre>
  
  <p>Basically, I'm trying to join the <code>person</code> and the <code>directs</code> table matching the <code>person_id</code> in both tables, while doing the same for <code>directs</code>. The problem is that the results from the second query aren't included (that Alfred Hitchcock with 10 movies has been bugging me from the start ).</p>
  
  <p>I'm using sqlite, I'd be very happy if someone can give me a push in the right direction. I was instructed also to avoid aggregrated subqueries. I had a solution that used them but it took 15 minutes to execute.</p>