MySQL查询显示结果，但我不能限制它们？

Hi I have a query like so

SELECT 
    videos.*, 
    categories.cat_name , 
    ( SELECT COUNT( * ) AS count FROM user_favorites WHERE user_id = 'tw415656866' AND video_id = videos.video_id )is_favorite 
FROM `videos` 
INNER JOIN categories 
    ON categories.cat_id = videos.cat_id 
WHERE 
    date <= '2011-11-21 09:12:18' 
GROUP BY videos.video_id 
ORDER BY (votesdown / votesup) ASC 
LIMIT 0, 5 

This code works fine and returns a table like below

video_id     - video id
cat_name   - category name
cat_id         - category id
title             - video title
yt_id           - youtubes video id
votesup      - votes up
votesdown  - votes down
date            - date added
tweeted       - 0 / 1 ( 1=tweeted)
is_favorite   - 0 / 1 ( 1=favorited)

What i'm trying to do is add this to the WHERE clause

AND is_favorite = 1

As you can see 'is_favorite' is added to the dataset as a column but I cannot query it because MySQL says the column 'is_favorite' doesn't exist.

The exact error is... " Unknown column 'is_favorite' in 'where clause' "

Any ideas?

Thanks alot

is_favorite is indeed not a column but an alias for your subquery. Try using

HAVING is_favorite = 1

instead. See for example http://www.w3schools.com/sql/sql_having.asp for an explanation of the difference between WHERE and HAVING

So your query should look like this:

SELECT 
    videos.*, 
    categories.cat_name , 
    ( SELECT COUNT( * ) AS count FROM user_favorites WHERE user_id = 'tw415656866' AND video_id = videos.video_id ) AS is_favorite 
FROM `videos` 
INNER JOIN categories ON categories.cat_id = videos.cat_id 
WHERE date <= '2011-11-21 09:12:18' 
GROUP BY videos.video_id 
HAVING is_favorite = 1
ORDER BY (votesdown / votesup) ASC 
LIMIT 0, 5 

Due to the order in which the query is processed, the is_favorite alias is not available at the time the WHERE clause is evaluated. You'd need to repeat the subquery in the WHERE.

Your query:

SELECT 
    videos.*, 
    categories.cat_name , 
    T.is_favorite 
FROM `videos` 
INNER JOIN categories 
    ON categories.cat_id = videos.cat_id 
Inner join 
    ( SELECT video_id, COUNT( * ) AS IsFavorite
      FROM user_favorites  
      WHERE user_id = 'tw415656866' 
      Group by video_id ) T 
        On  T.video_id = videos.video_id 
WHERE 
    date <= '2011-11-21 09:12:18' 
  And T.isFavorite =1
GROUP BY videos.video_id 
ORDER BY (votesdown / votesup) ASC 
LIMIT 0, 5