title: How to get outer column value in subquery
body: <p>I am Implementing a Social networking website using php and mysql and I am stuck with a query to get notification.</p>
  
  <p>My <strong>notification</strong> table has these columns:</p>
  
  <blockquote>
    <p>user_id,post_id,type(<em>defines weather its a comment(value 2) or a
    post(1)</em>),</p>
    
    <p>status(<em>0 read 1 not read</em>),msg(for optional message)</p>
  </blockquote>
  
  <p><strong>comments</strong> table:</p>
  
  <blockquote>
    <p>comment_id ,  user_id ,   post_id, comment ,  time ,  status</p>
  </blockquote>
  
  <p><strong>like</strong> table:</p>
  
  <blockquote>
    <p><strong>user_id</strong> ,    <strong>post_id</strong>(can store either post_id from post table or
    comment_id from comment table) ,  <strong>type</strong>(<em>same as type of notification</em>) 
    <em>all three columns makes a unique combo</em></p>
  </blockquote>
  
  <p>now I want to get the notification output like:</p>
  
  <blockquote>
    <p>notification_id    |     message</p>
    
    <p>1    |  3 peoples Liked your post</p>
    
    <p>2     |   2 peoples Commented on your post</p>
  </blockquote>
  
  <p>I created this query</p>
  
  <pre><code>SELECT `notification_id`,
      CASE 
      WHEN `msg` IS NOT NULL THEN msg
      WHEN `type`=1 THEN concat((SELECT count(user_id) from `likes` WHERE `post_id`=post_id AND `type`=1 AND `user_id`!='8'),' peoples Liked your post')
      WHEN `type`=2 THEN concat((SELECT count(*) from (SELECT DISTINCT `user_id` FROM `comments` WHERE `post_id`=post_id AND `user_id`!='8') AS commenters),' peoples Commented on your post')
      END AS message
  FROM
      `notifications` WHERE `user_id`='8' -- 8 is just a user_id I was testing on.
  </code></pre>
  
  <p>it seems to be working fine until I realized the values were incorrect and I soon realized my mistake was in </p>
  
  <pre><code>`post_id`=post_id
  </code></pre>
  
  <p>which is true all the time so all of the rows are counted.</p>
  
  <p>I searched for getting outer column value but couldn't find a solution ,Can you please help me how to achieve it.</p>