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