Fahad Kazmi Fahad Kazmi - 4 months ago 9
MySQL Question

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.

Answer

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.