MySQL find rows based on another query-CodePudding

Let's say I have 2 tables wp_posts and wp_postmeta.

I was able to run this SQL query
SELECT * FROM `wp_postmeta` WHERE meta_key = 'waterfront' AND meta_value = 'No';

And this is the result of the query which is great.

| meta_id | post_id | meta_key   | meta_value |  |
 --------- --------- ------------ ------------ -- 
| 67      | 1       | waterfront | No         |  |
 --------- --------- ------------ ------------ -- 
| 1122    | 90      | waterfront | No         |  |
 --------- --------- ------------ ------------ --

How can I get the rows from the wp_posts table with the post_id that I'm getting on the first query.

I thought to try another query, something like:
SELECT * FROM `wp_posts` WHERE id IN (1, 90)

But I probably do it wrong, it's not like I can run the first query and save it to a variable and make another query based on that.

CodePudding user response：

You may achieve what you want by running one of the queries below:

using inner join

SELECT wp.* FROM wp_posts wp INNER JOIN wp_postmeta wpm ON wpm.post_id = wp.id AND meta_key = 'waterfront' AND meta_value = 'No';

using in

SELECT * FROM wp_posts WHERE id IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'waterfront' AND meta_value = 'No');