CODEWITHSUNDEEP
mysql
philipp
philipp

Reputation: 16515

mysql combined query, ids from comma separated list

I am not that familiar with mysql, so do not exactly not how sample the query correctly. I am working with wordpress and to illustrate what I would like to select please see the image below:

scetch of mysqlquery

I have the ID of one special post. I would like to get all other Posts that are connected to it in the following way:

In the posts_meta table can exist entries, whose post_id matches the given ID. One of these entries meta_key is of a certain value and the meta_value of that very entry is a comma separated list of post_id's. I would like to Select * for each of Posts whose ID is in that comma separated list

I started with the following:

SELECT *
FROM prefix_posts AS a
JOIN prefix_postmeta AS b ON ( a.ID = b.post_id )
WHERE … //??

but I have no idea how to finish the query. How can I select the ids, to select posts in turn? I found SO answers dealing with FIND_IN_SET, but I do not want to test if a given ID is in the set, I the more need something like »STR_SPLIT« in a subquery.

Upvotes: 3

Views: 151

Answers (1)

Marcassin
Marcassin

Reputation: 1405

I would like to Select * for each of Posts whose ID is in that comma separated list

Why not using FIND_IN_SET method ?, it can help you for your problem This query return all posts whose have id in meta_value field from meta row having your "special post id" as post_id.

SELECT * FROM Post a, meta b
WHERE FIND_IN_SET(a.id, b.linked)>0 AND b.post_id= $id_post;

Tried on SQLFiddle, it seems to work. Add a GROUP BY clause if you want avoid duplicates.

Upvotes: 1

Related Questions