how to use value from main query in sub query

Question

Im trying to run a sub-query that based on one of the main query values but i always get 0 as VALUE. This is my query :

SELECT ID,(
SELECT COUNT( * ) 
    FROM  `post_meta` 
    WHERE  `post_id` 
    IN (
        SELECT  `ID` 
        FROM  `wp_posts` 
        WHERE  `post_title` = posts.ID
    )
) AS counter
FROM wp_posts;

if i run only the sub-query with id number instead of posts.ID it returns a good value.

Answer 1

I do believe a simple join in the sub query will get you the correct COUNT:

SELECT posts.ID,
(
  SELECT COUNT(*) 
  FROM post_meta
  INNER JOIN wp_posts ON wp_posts.ID = post_meta.post_ID
  WHERE wp_posts.post_title = posts.ID  
) AS counter
FROM posts;

Answer 2

The problem was fixed by giving the table a custom name so i can use it when im going in two layers this is how the code look after the change :

SELECT ID,(
SELECT COUNT( * )
    FROM  `post_meta` 
    WHERE  `post_id` 
    IN (
        SELECT  `ID` 
        FROM  `wp_posts` 
        WHERE  `post_title` = my_posts.ID
    )
) AS counter
FROM wp_posts as my_posts;