CODEWITHSUNDEEP
mysqljoinsubquerycorrelated-subquery
CatamaranDan
CatamaranDan

Reputation: 83

mySQL correlated Subquery

trying to write a mysql query and having a lot of difficult with this one.

I have two tables( Item: info about items, and itemReview: reviews for the items )

What I would like to do is select all the items that belong to a particular location (which is what my outer query does) and then for each item in the outer query, get the average of all the rating fields in the itemReview table

Here is my attempt:

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    sub.rating
FROM Item 
JOIN (
        SELECT 
            AVG(ItemReview.rating) AS rating 
        FROM ItemReview 
        WHERE ItemReview.fidItem = Item.idItem
    ) AS sub
WHERE Item.fidLocation = '63';

but mySQL says: Unknown column 'Item.idItem' in 'where clause'

Any help would be very appreciated!! thanks!!

Upvotes: 3

Views: 3530

Answers (6)

Programador Adagal
Programador Adagal

Reputation: 780

What about this:

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    sub.rating
FROM Item myTable
JOIN (
        SELECT 
            AVG(ItemReview.rating) AS rating 
        FROM ItemReview 
        WHERE ItemReview.fidItem = myTable.idItem
    ) AS sub
WHERE Item.fidLocation = '63';

Here you are giving a name to main query and referencing the new table name in the subquery. Using the original table names causes MySQL don't know about what table take.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT i.idDish, i.dateAdded, i.dateModified, i.fidLocation, i.category, 
       i.description, i.price, i.name, i.fullImageName, 
       i.thumbnailImageName, ir.rating 
FROM Item i 
LEFT JOIN (SELECT fiditem, AVG(ItemReview.rating) AS rating 
           FROM ItemReview
           GROUP BY fiditem) ir ON ir.fidItem = i.idItem 
WHERE i.fidLocation = '63';

Upvotes: 0

Taryn
Taryn

Reputation: 247860

You are trying to access the Item.idItem inside of the subquery but it is not available there. You should use something like this:

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    sub.rating
FROM Item 
JOIN 
(
    SELECT fidItem, AVG(ItemReview.rating) AS rating 
    FROM ItemReview 
    GROUP BY ItemReview.fidItem
) AS sub
   ON sub.fidItem = Item.idItem
WHERE Item.fidLocation = '63';

Upvotes: 7

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    sub.rating,
    Item.idItem
FROM Item 
JOIN (
        SELECT 
            AVG(ItemReview.rating) AS rating ,
            fidItem
        FROM ItemReview 
        GROUP BY fidItem
    ) AS sub on sub.fidItem = Item.idItem
WHERE Item.fidLocation = '63';

Upvotes: 0

a1ex07
a1ex07

Reputation: 37382

You cannot refer to "outer" tables in such way. One solution is already proposed by bluefeet, another which will also work in your case :

SELECT 
Item.idDish, 
Item.dateAdded, 
Item.dateModified, 
Item.fidLocation, 
Item.category, 
Item.description, 
Item.price, 
Item.name, 
Item.fullImageName, 
Item.thumbnailImageName, 
(SELECT AVG(ItemReview.rating) AS rating 
    FROM ItemReview 
    WHERE ItemReview.fidItem = Item.idItem
) as rating
FROM Item 
WHERE Item.fidLocation = '63';

Upvotes: 0

Justin
Justin

Reputation: 9724

Query:

SELECT 
    Item.idDish, 
    Item.dateAdded, 
    Item.dateModified, 
    Item.fidLocation, 
    Item.category, 
    Item.description, 
    Item.price, 
    Item.name, 
    Item.fullImageName, 
    Item.thumbnailImageName, 
    (   SELECT 
            AVG(ItemReview.rating) AS rating 
        FROM ItemReview 
        WHERE ItemReview.fidItem = Item.idItem
    ) AS rating
FROM Item 
WHERE Item.fidLocation = '63'

Upvotes: 4

Related Questions