CODEWITHSUNDEEP
mysqlsubquery
user2326568
user2326568

Reputation: 355

how to use value from main query in sub query

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.

Upvotes: 10

Views: 22327

Answers (3)

user2326568
user2326568

Reputation: 355

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;

Upvotes: 2

jjs9534
jjs9534

Reputation: 475

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

Upvotes: 1

Linger
Linger

Reputation: 15068

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;

Upvotes: 5

Related Questions