Menu
I can't for the life of me figure out what's wrong with this SQL statement and why it's not producing any results. If I take out the LEFT JOIN is works, so what's wrong with it?
SELECT b.id, r.avg_rating
FROM items AS b
LEFT JOIN
(
SELECT avg(rating) as avg_rating
FROM ratings
GROUP BY item_id
) AS r
ON b.id = r.item_id
WHERE b.creator = " . $user_id . "
AND b.active = 1
AND b.deleted = 0
ORDER BY b.order ASC, b.added DESC
Would appreciate the help greatly.
881
Move the 'Reserve' table named in the subquery to the FROM clause and join it to 'Customers' using LEFT JOIN. The WHERE clause compares the customer_id column to the ids returned from the subquery. Hence convert the IN expression to an explicit direct comparison between id columns of two tables in the FROM clause.
Joins and subqueries are often used together in the same query. In many cases, you can solve a data retrieval problem by using a join, a subquery, or both.
Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.
add the item_id column in your subquery (I guarantee that it will work) so the ON clause can find r.item_id
SELECT item_id, avg(rating) as avg_rating
FROM ratings
GROUP BY item_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
