MySQL error in where clause : Unknown column

MySQLselect

When I try to execute the query below, I receive the error message:

Unknown column 'have_discount' in 'where clause'

My query is:

SELECT
    DISTINCT(MP.item_id), item_title, item_user_id, item_price, item_discount_price, url_slug, 
    thumbnail_img, thumbnail_ext, thumbnail_width, thumbnail_height, is_free_item, default_img, 
    default_img_ext, default_img_width, default_img_height, item_category_url_str, 

    IF( ( DATE( NOW() ) BETWEEN item_discount_fromdate AND item_discount_todate), 1, 0 ) AS have_discount,

    item_category_id, NOW() as date_current, date_activated, MP.total_downloads, 
    MP.item_sold, MP.total_views, MP.total_comments, MP.total_likes AS likes, 
    MP.total_dislikes, MP.total_rating, MP.rating_count, MP.total_users_rated, 
    (MP.total_rating/MP.rating_count) as rating, MP.default_img_ext , MPL.item_title, 
    MPL.lang_id, MPL.item_description, MPL.item_highlight_text, MP.user_section_id, is_downloadable_item 

FROM mp_item AS MP, mp_item_lang AS MPL

WHERE have_discount = 1
AND item_status = 'Ok'
AND MP.item_id = MPL.item_id
AND item_user_id = 3
AND MPL.lang_id = 2
ORDER BY date_activated DESC
LIMIT 0,12

I am trying to list items with a discount. How can I do this?

Best Answer

Now, if you rewrite the query in a clear manner, you can see where's the troublesome column. From what I know of general SQL you can't use the alias of the column in the WHERE clause (but you can use it in the ORDER BY clause).

So you'd have to rewrite the query something like:

Select *
from (
    -- Your nice query without the have_discount filter
) as Aux
WHERE Aux.have_discount = 1
ORDER BY date_activated DESC
LIMIT 0,12

Related Solutions

Mysql – Query WHERE clause and JOIN

Here is your original query

SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = `CallEventID`
LIMIT 1000

In theory, you can do the following

Subquery that has WHERE on the StartTime
Add ORDER BY StartTime
Do the LIMIT inside the subsquery
Specify a LEFT JOIN

Here is such a query

SELECT * FROM
(
    SELECT * FROM `CallDetailRecord`
    WHERE `StartTime` >=1357102799000
    AND `StartTime` <=1357880399000
    ORDER BY StartTime
    LIMIT 0,1000
) A LEFT JOIN `CallEvent` B ON `EventID` = `CallEventID`;

If the query is slow, then add an index on StartTime

ALTER TABLE CallDetailRecord ADD INDEX (StartTime);

Give it a Try !!!

MySQL Performance – DATE vs. time_field in WHERE Clause

Just addressing this question

What's the difference between time_field > 'x' AND DATE(time_field) > 'x' inside WHERE clause?

The difference is how the Query Optimizer treats them.

If the WHERE clause has time_field > 'x', this signals the Query Optimizer to try looking for any index so as to take advantage of it doing a range scan.
If the WHERE clause has DATE(time_field) > 'x', this signals the Query Optimizer to throw all indexes under the bus because the DATE function has to be called across the whole table (in the form of a full table scan) or the join result.

Depending on the key distribution of time_field, DATE(time_field) > 'x' triggering a full table scan just happens to be better than a range scan on an index if the values make up a significant percentage of the index. This is even more true using InnoDB because a full table scan passes through the clustered index (where PRIMARY KEY and row data coexist), while a secondary index on time_field would cause a lookup of the secondary index in addition to the clustered index. If such a secondary index had a lopsided key distribution, such an index would be ignored in favor of a full table scan anyway.

This conjuecture is only indicative of your current dataset. Someone else's dataset may have a better (evenly distributed, more balanced) key distribution, resulting in time_field > 'x' working better than DATE(time_field) > 'x'.

To see such differences, run the EXPLAIN on both SELECT queries. The results may be different.

Best Answer

Related Solutions

Mysql – Query WHERE clause and JOIN

MySQL Performance – DATE vs. time_field in WHERE Clause

Related Question