CODEWITHSUNDEEP
mysql
cocojambo
cocojambo

Reputation: 101

MySQL find rows based on another query

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.

Upvotes: 1

Views: 698

Answers (1)

Paulo Pereira
Paulo Pereira

Reputation: 1115

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');

Upvotes: 3

Related Questions