CODEWITHSUNDEEP
phpmysqljoin
Syed Aqeel
Syed Aqeel

Reputation: 1009

mysql - right joining two tables as one to many relation

I know that this question already asked many times here but after all I could't found my answer that what I want.

My Question is:

I have two tables and the structure of these tables is as:

table1: item_id, store,title,available,shipping

table2: item_id, review_rate,user_id,review_title

These tables should be join as one to many relation.

For example if the data in these tables is as:

table1:

item_id      store      title    available    shipping
-------------------------------------------------------
11           glasses    ..........................
12           dresses    ..........................
.
.
.

table2:

item_id     review_rate    user_id    review_title
--------------------------------------------------
11           3              10023      good item
11           5              10024      nice item
12           1              10024      nice one
.
.
.

then the result should be as after joining:

afterJoin:

item_id   store   title   available   shipping   rate   people_reviewed
-----------------------------------------------------------------------
11        ....................................   4      2
12        ....................................   1      1

The query I tried to join is as:

CREATE OR REPLACE VIEW afterJoin AS 
SELECT i.*,round(AVG(r.review_rate)) as rate,count(r.user_id) as people_reviewed 
   FROM table1 i 
   RIGHT JOIN  table2 r ON i.item_id = r.item_id

but this return only one row.

Upvotes: 0

Views: 55

Answers (1)

Matt S
Matt S

Reputation: 15364

Your query is missing a GROUP BY clause. Without it your database is aggregating all of the records together.

SELECT i.*, round(AVG(r.review_rate)) as rate, count(r.user_id) as people_reviewed 
FROM table1 i 
    RIGHT JOIN  table2 r ON i.item_id = r.item_id
GROUP BY i.item_id

The GROUP BY instructs the db to aggregate for each item_id.

Upvotes: 2

Related Questions