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.
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;
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;
Linger you beat me to it but I was going to suggest leaving out the subquery altogether if I can guess what the structure looks like.
SELECT ID, COUNT(*) AS count FROM wp_posts
INNER JOIN post_meta ON wp_posts.ID = post_meta.post_id
WHERE wp_posts.ID = post_title
GROUP BY ID
OR
SELECT COUNT(*) AS count FROM wp_posts
INNER JOIN post_meta ON wp_posts.ID = post_meta.post_id
WHERE wp_posts.ID = post_title
wp_posts.IDdue to your comment on jjs9534 answer. So, how can you havewp_posts.ID = post_meta.post_idand alsowp_posts.ID = post_meta.post_title? That just doesn't seem right.post_title= posts.ID topost_title= 5161 it works fine, so this part is ok,post_meta.post_idis always populated then you couldn't you just leave off thewp_posts.ID = post_meta.post_titlecompletely. Part of the problem might be your comparing two different data types.