How to select from multiple tables based on condition

Question

I have two tables:

post table:

+-----+------------+--------------+
| id  |   title    |   details    |
+-----+------------+--------------+
| 185 | some title | some details |
| 186 | some title | some details |
+-----+------------+--------------+

post category:

+----+------------+---------+
| id |  category  | post_id |
+----+------------+---------+
|  1 | some title |     185 |
|  2 | some title |     186 |
+----+------------+---------+

When the user clicks on category, I want to get all posts from post table based on category selected.

I am able to select category and post_id like this:

List<Map> postCategories2 = Base.findAll("select category, post_id from posts_categories where category = ?", request.queryParams("category_name"));

But what I want is to use a single query and select id, title, details from post table using the category and post_id from table 2 i.e category table

All the information I have is category name i.e request.queryParams("category_name")

Note: id and post_id have primary key-foreign key relationship

Answer 1

I think you have to use a join for that Replace your query with this query

select title,details,category from post p inner join posts_categories c on 
 p.id=c.post_id where category= ?//your category name at the question mark

hope this helps