-1

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

kittu
  • 6,662
  • 21
  • 91
  • 185

1 Answers1

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

Saurabh Gupta
  • 122
  • 2
  • 12
  • I don't have `category` in post table. Also what does 'c' mean in `posts_categories c` ? – kittu Jun 17 '17 at 12:08
  • 1
    I have actually use join . category is the name of the column and c is just the object of posts_categories table just to not write the name of that table again and again. I have actually fetched title and details from post table and category from post_categories table – Saurabh Gupta Jun 17 '17 at 12:10