php - Combine two SELECT queries from different tables -

issue

i have 1 table (posts) articles , article meta.

another table (post_reviews) contains user-submitted ratings (a value out of 5) each article, referencing posts id of post in question.

i trying find top 3 posts, review, of last 3 days. therefore need to:

• find posts in time period (3 days)
• find average rating each post
• sort them average rating (desc)

code

for first part, can use query:

select * `posts` `hub_id`=:hub_id , `date`>=:start_date , `date`<=:end_date) 

to find each individual post's average rating, use query:

select sum(`review`) `total` `post_reviews` `id`=:id 

then number of rows work out average:

select * `post_reviews` `post_id`=:id 

how can combine these three, or process data can order posts in time period average rating?

answer

the end result looks this:

select   `posts`.`id`, avg(`post_reviews`.`review`) `average`     `posts`  join     `post_reviews` on (`posts`.`id`=`post_reviews`.`post_id`)    `hub_id`=:hub_id     , `posts`.`date`>=:start_date     , `posts`.`date`<=:end_date group `post_id` order avg(`review`) desc 

not sure hub_id represents, assume it's necessary; assume key field in posts posts.post_id , not posts.id:

select   `p`.`id`, avg(`pr`.`review`) `average`     `posts`        `p` join     `post_reviews` `pr` on (`p`.`id`=`pr`.`post_id`)    `hub_id` =:hub_id      , `p`.`date` between current_date-3 , current_date group `p`.`id` order avg(`review`) desc; 

see example: sqlfiddle