Canman2005 Posted June 19, 2010 Share Posted June 19, 2010 Hi all Any ideas why my QUERY SELECT i.id, i.date, (SELECT COUNT(*) FROM `sold` WHERE item_id = i.id) AS total FROM items i WHERE total > 1 does not work? it's being a nightmare and cannot figure out what its problem is Thanks Dave Quote Link to comment Share on other sites More sharing options...
cags Posted June 19, 2010 Share Posted June 19, 2010 I'm not sure you can use generated fields in the WHERE clause, trying changing WHERE to HAVING. Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted June 19, 2010 Author Share Posted June 19, 2010 ok thanks, ill have a hunt around Quote Link to comment Share on other sites More sharing options...
cags Posted June 19, 2010 Share Posted June 19, 2010 I'm not certain HAVING will work without a GROUP BY clause though. In future if you define what exactly 'not working' means, e.g. "I got this error message.", or "There's no error message, but it's not returning any rows.". Then we can provide better answers right off the bat. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.