JOIN query with IN operator

A post have many comments,

I want to select only posts wich have comments with a certain id (ex: (2, 4, 6, 7))

I tried the following sql query:

SELECT wp_posts.* FROM wp_posts

LEFT JOIN wp_comments ON wp_comments.comment_ID IN (2, 4, 6, 7);

The query return me 开发者_JAVA技巧weird results.

Can someone tell ma what wrong with this query ? thanks in advance

Try this:

SELECT P.* 
FROM wp_posts AS P
INNER JOIN wp_comments AS C ON C.post_ID = P.post_ID
WHERE C.comment_ID IN (2, 4, 6, 7);

Modify the names of the columns for post_id in ON C.post_ID = P.post_ID as required; I'm not sure of the WordPress exact column names.

That is not a JOIN condition, it should be in the WHERE clause.

It would be better if we could see your schema. I think your looking for something like this

SELECT wp_posts.* FROM wp_posts
LEFT JOIN wp_comments ON wp_comments.postid = wp_posts.id
WHERE wp_comments.comment_ID IN (2, 4, 6, 7);