title: SQL subquery logic confusion
body: <p>I'm writing an SQL query but I'm stuck at a point and can't figure out how to solve this issue. First have a look at the query below:</p>
  
  <pre><code>SELECT user_id, COUNT(*) as count 
  FROM hwc_attend 
  WHERE at_id IN 
      (SELECT evdet_id
       FROM eve_detail
       WHERE evdet_id IN (SELECT at_id FROM hwc_attend WHERE attendstate=1 )
         AND location &lt;&gt; ''
         AND evdet_id &gt; 999
         AND location NOT IN (SELECT ASIN FROM pReviews )
      )
  GROUP BY user_id
  </code></pre>
  
  <p>This query is working fine but giving lesser results than required because the part <code>SELECT ASIN FROM pReviews</code> should be like <code>SELECT ASIN FROM pReviews where cID={place current value of "location" field from table eve_detail here</code>.</p>
  
  <p>For a better understanding, here's the errornous query:</p>
  
  <pre><code>SELECT user_id, COUNT(*) as count 
  FROM hwc_attend 
  WHERE at_id IN 
      (SELECT evdet_id **, location**
       FROM eve_detail
       WHERE evdet_id IN (SELECT at_id FROM hwc_attend WHERE attendstate=1)
         AND location &lt;&gt; ''
         AND evdet_id &gt; 999
         AND location NOT IN (SELECT ASIN FROM pReviews where cID=**location**)
      )
  GROUP BY user_id
  </code></pre>
  
  <p>It's hard to explain.. In short, I have to remove "location" values from the result fetched from table "eve_detail" that also exist in table "pReviews" in column cID.</p>
  
  <p>Additionally, it would be nice if someone can covert it into joins. I would need both queries for learning.</p>