#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    219
    Rep Power
    11

    Question Help w/ Adding Search Field in Complex Query


    I've got a pretty complex MySQL query running on a live site. And I need to add an additional search field -- but I keep coming with an unkown column in field list error.

    Here's the existing query:
    Code:
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, TRUNCATE(ROUND(AVG(i.itemGrade) * 2) / 2,1) AS fieldMusicGrade, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, SUM(i.itemTime) AS fieldTotalTime, (IF(f.fieldMusicOnly != '', f.fieldMusicOnly, SUM(i.itemPrice)) + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice
    FROM field AS f
    INNER JOIN items AS i ON i.itemID IN (f.fieldMusic1, f.fieldMusic2, f.fieldMusic3, f.fieldMusic4, f.fieldMusic5)
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre
    WHERE f.fieldEnabled = 'Yes' AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) AND (f.fieldTitle LIKE '%test%' OR f.fieldSynopsis LIKE '%test%' OR f.fieldNotes LIKE '%test%' OR f.fieldKeywords LIKE '%test%')
    AND NOT (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '')
    GROUP BY f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldTitle, f.fieldDrillGrade, g.genreName
    UNION ALL
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, fieldMusicGrade AS fieldMusicGradeAvg, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, (f.fieldTime1 + f.fieldTime2 + f.fieldTime3 + f.fieldTime4 + f.fieldTime5) AS fieldTotalTime, (f.fieldMusicOnly + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice
    FROM field AS f
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre
    WHERE f.fieldEnabled = 'Yes' AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) AND (f.fieldTitle LIKE '%test%' OR f.fieldSynopsis LIKE '%test%' OR f.fieldNotes LIKE '%test%' OR f.fieldKeywords LIKE '%test%')
    AND (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '')
    ORDER BY fieldTitle
    The new field I need to also search now is called i.itemComposer from the items table. When I add:
    Code:
     OR i.itemComposer LIKE '%$searchText%'
    To the WHERE clause -- I get an unknown column error.

    How do I need to go about adding this? Thank you for any help you can provide.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    To which WHERE clause are you adding this? If it's the one after the UNION then there is no reference to that table in that query.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    219
    Rep Power
    11

    Question


    Okay. I just added the i.itemComposer search field to the first WHERE clause. And it now returns results. Here's the revised query:
    Code:
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, TRUNCATE(ROUND(AVG(i.itemGrade) * 2) / 2,1) AS fieldMusicGrade, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, SUM(i.itemTime) AS fieldTotalTime, (IF(f.fieldMusicOnly != '', f.fieldMusicOnly, SUM(i.itemPrice)) + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice 
    FROM field AS f 
    INNER JOIN items AS i ON i.itemID IN (f.fieldMusic1, f.fieldMusic2, f.fieldMusic3, f.fieldMusic4, f.fieldMusic5) 
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade 
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre 
    WHERE f.fieldEnabled = 'Yes' 
    AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) 
    AND (f.fieldTitle LIKE '%$searchText%' OR f.fieldSynopsis LIKE '%$searchText%' OR f.fieldNotes LIKE '%$searchText%' OR f.fieldKeywords LIKE '%$searchText%' OR i.itemComposer LIKE '%$searchText%') 
    AND NOT (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '') 
    GROUP BY f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldTitle, f.fieldDrillGrade, g.genreName 
    UNION ALL 
    SELECT f.fieldID, f.fieldNew, f.fieldFeatured, f.fieldSale, f.fieldSaleAmount, f.fieldTitle, f.fieldDrillGrade, fieldMusicGrade AS fieldMusicGradeAvg, g.genreName, (f.fieldSets1 + f.fieldSets2 + f.fieldSets3 + f.fieldSets4 + f.fieldSets5) AS fieldTotalSets, (f.fieldTime1 + f.fieldTime2 + f.fieldTime3 + f.fieldTime4 + f.fieldTime5) AS fieldTotalTime, (f.fieldMusicOnly + IF(f.fieldDrillOnly != '', f.fieldDrillOnly, o.optionPrice)) AS fieldTotalPrice 
    FROM field AS f 
    LEFT OUTER JOIN options AS o ON o.optionPerDrillGrade = f.fieldDrillGrade 
    LEFT OUTER JOIN genres AS g ON g.genreID = f.fieldGenre 
    WHERE f.fieldEnabled = 'Yes' 
    AND (o.optionID = 662 OR o.optionID = 663 OR o.optionID = 664 OR o.optionID = 665) 
    AND (f.fieldTitle LIKE '%$searchText%' OR f.fieldSynopsis LIKE '%$searchText%' OR f.fieldNotes LIKE '%$searchText%' OR f.fieldKeywords LIKE '%$searchText%') 
    AND (COALESCE(fieldMusicTitle1,'') > '' OR COALESCE(fieldMusicTitle2,'') > '' OR COALESCE(fieldMusicTitle3,'') > '' OR COALESCE(fieldMusicTitle4,'') > '' OR COALESCE(fieldMusicTitle5,'') > '') 
    ORDER BY fieldTitle
    The new issue is that the fieldTotalPrice is wrong when you find matches with the i.itemComposer field. If you find a match with any of the other search fields -- it's fine. I'm assuming it has to do with the group/union and the fact that the items table isn't used in the second half of the query?

    Any suggestions on getting around this? Thank you again for all your help.
    Last edited by msmith29063; January 2nd, 2014 at 09:23 PM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Wrong how?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    219
    Rep Power
    11
    The price (fieldTotalPrice) is generated by adding the prices of the entries from the items table (linked via f.fieldMusic1, etc.) as long as there is no "override" price (f.fieldMusicOnly). If there is an "override" -- the price is calculated differently. That's why there is a group/union. The price for each record to be displayed is calculated one of these two ways.

    * When I run the query with no search text ($searchText) -- all the records display with the correct price.

    * If I use search text that is found in one of the i.itemComposer fields (items table) -- and there is a price "override" (f.fieldMusicOnly) -- the price displays properly.

    * If I use search text that is found in one of the i.itemComposer fields (items table) -- and there is NO price "override" (f.fieldMusicOnly) -- the price displays wrong. It appears to only be using the price (i.itemPrice) of the items record that has the i.itemComposer match -- and not the other linked items. It should still add all the prices of the linked items.

    So it appears that the i.itemComposer LIKE '%$searchText%' is incorrect or in the wrong place. Anyone know how to address this?

    Sorry -- I've had help with the query up to this point and it's a little over my comfortability level with SQL queries.

    Thank you for your help.
    Last edited by msmith29063; January 3rd, 2014 at 01:40 AM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    The only thing that the WHERE clause will be doing to your calculation is bringing back different rows to be processed. I'd be willing to guess that some of those rows contain columns that you reference in the calculation which is causing mySQL to 'choke' in a predictable and defined way (such as non-numerics like spaces and NULLs)
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    219
    Rep Power
    11
    What should I look for? Not sure how to proceed from here. Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo