CODEWITHSUNDEEP
mysql
Bodi
Bodi

Reputation: 271

Why does my SQL query give this error?

I can't figure out why I keep getting error #1054 - Unknown column 'Filename' in 'field list'

SELECT 'R' AS Type, er.ID, er.RecipeID AS RecipeID,
    RecipeName AS Name,er.Quantity,UnitID, 
    SUBSTRING_INDEX(PFCC,',',1) AS Protein,
    SUBSTRING_INDEX(SUBSTRING_INDEX(PFCC,',',2),',',-1) AS Fat, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(PFCC,',',-2),',',1) AS Carbs,
    SUBSTRING_INDEX(PFCC,',',-1) AS Calories, 
    Rating, AvgRating, AvgCount, 0 AS SpecificID, 
    COALESCE(rimg.Filename,'0.png') AS DefaultImage, 
    s.Price, s.MaxQuantity 
FROM `event-recipe` er 
LEFT JOIN(
      SELECT r.ID,RecipeName,
          SUM(Quantity) AS Quantity, 
          IFNULL(Rating,0) AS Rating
      FROM recipe r 
           LEFT JOIN(
              SELECT RecipeID, Filename 
              FROM recipe_image 
              WHERE IsDefault) rimg 
              ON r.ID=rimg.RecipeID 
           LEFT JOIN `recipe-ingredient` ri 
              ON r.ID=ri.RecipeID 
           LEFT JOIN(
              SELECT RecipeID,Rating 
              FROM `recipe-rating` 
              WHERE CustomerID=2) rr 
              on r.ID=rr.RecipeID 
     GROUP BY r.ID) r ON er.RecipeID=r.ID 
LEFT JOIN(
     SELECT RecipeID, GROUP_CONCAT(Value ORDER BY Nutr_No) AS PFCC 
     FROM nutrient_view_ingredient nv 
     WHERE Nutr_No IN (203,204,205,208) 
     GROUP BY RecipeID) n 
     ON r.ID=n.RecipeID 
LEFT JOIN sell_recipe s ON (er.EventID=s.EventID AND s.RecipeID=er.RecipeID) 
LEFT JOIN(
     SELECT RecipeID, COUNT(RecipeID) AS AvgCount, ROUND(AVG(Rating),1) AS AvgRating 
     FROM `recipe-rating` 
     GROUP BY RecipeID) avgrate 
     ON r.ID=avgrate.RecipeID 
WHERE er.EventID=53

Upvotes: 0

Views: 44

Answers (1)

AdamMc331
AdamMc331

Reputation: 16691

The issue is in how you've used your parentheses to group your table, as well as the fact that you never select filename in the inner join.

The first big join, which you've named r is the table that has reference to filename. No where in that table do you select rimg.fileName so in the outer query, you can't even call r.fileName. To fix it, change the line:

SELECT r.ID, RecipeName

To:

SELECT r.ID, RecipeName, rimg.FileName

Then, in your outer query, you can select r.FileName.

Upvotes: 2

Related Questions