MySQL SELECT using WHERE and OR
Posted by HostRefugee-Vince, 03-23-2007, 08:47 PM Hi, The following SELECT statement is giving me undesired results: SELECT * FROM `fa_referrals` WHERE `status` = 0 OR 2 OR 3 OR 6 The select statement returns every row, regardless of the status. As you can probably tell from the statement, I only want rows returned if status = 0, 2, 3 OR 6. Where did I go wrong?
Posted by stuffradio, 03-23-2007, 08:49 PM SELECT * FROM `fa_referrals` WHERE `status` = '0' OR `status`='2' OR `status` = '3' OR `status`='6'
Posted by zacharooni, 03-23-2007, 08:49 PM SELECT * FROM `fa_referrals` WHERE status=0 OR status=2 OR status=3 OR status=6
Posted by HostRefugee-Vince, 03-23-2007, 08:55 PM Thanks everyone I knew I was missing something.
Posted by HostRefugee-Vince, 03-23-2007, 09:27 PM Ok, time to add some more complexity Another SELECT with Undesired Results SELECT * FROM `fa_referrals` WHERE `dateActivated` >= DATE_SUB(curdate(), INTERVAL 90 DAY) AND `status` = 0 OR `status` = 2 OR `status` = 3 OR `status` = 5 OR `status` = 6 That query should pull only rows where the dateActivated date is less than 90 days old AND the status = 0, 2, 3, 5 or 6. It's pulling the right rows in terms of "status" but it's not paying attention to the date.
Posted by stuffradio, 03-23-2007, 10:03 PM this thread might help you: http://www.jaguarpc.com/forums/showthread.php?t=15888
Posted by HostRefugee-Vince, 03-23-2007, 10:47 PM Honestly, that thread confuses me more than anything. I was able to get my query to work by using HAVING, but the people in the other thread question the original posters use of "HAVING". This statement returns the correct results: SELECT * FROM `fa_referrals` WHERE `status` = 0 OR `status` = 2 OR `status` = 3 OR `status` = 5 OR `status` = 6 HAVING `dateActivated` >= DATE_SUB(curdate(), INTERVAL 90 DAY) Am I using HAVING correctly?
Posted by HostRefugee-Vince, 03-23-2007, 10:51 PM Hmm, it seems if I take out the OR's in the above statement... I can get the desired results as well. That would mean I would have to run a separate statement for each 'status'. SELECT * FROM `fa_referrals` WHERE `dateActivated` >= DATE_SUB(curdate(), INTERVAL 90 DAY) AND `status` = 3 Is it against the rules to use AND and OR in the same SELECT? P.S. I don't want to run multiple statements. I would like to accomplish my goal with only 1 statement. The statement using HAVING in my previous post works... I just want to make sure I am using HAVING correctly. If I am not, how can I get the desired results using WHERE, AND and OR
Posted by stuffradio, 03-23-2007, 11:06 PM No, why would it be against the rules?
Posted by HostRefugee-Vince, 03-23-2007, 11:26 PM I don't know why it would be against the rules.... I am more or less trying to understand why the following query ignores the dateActivated rule, but processes the status rules just fine: SELECT * FROM `fa_referrals` WHERE `dateActivated` >= DATE_SUB(curdate(), INTERVAL 90 DAY) AND `status` = 0 OR `status` = 2 OR `status` = 3 OR `status` = 5 OR `status` = 6 Although my other statement that uses HAVING returns the desired results.... Isn't HAVING only supposed to be used when you GROUP BY?
Posted by HostRefugee-Vince, 03-24-2007, 12:39 AM Mark this one as resolved!!! Using some parantheses around the OR statements does the trick: SELECT * FROM `fa_referrals` WHERE `dateActivated` >= DATE_SUB(curdate(), INTERVAL 90 DAY) AND (`status` = 0 OR `status` = 2 OR `status` = 3 OR `status` = 5 OR `status` = 6)
Posted by SingularityStudios, 03-24-2007, 02:36 AM For simplicty's sake, you could write that as: SELECT * FROM `fa_referrals` WHERE `dateActivated` >= DATE_SUB(curdate(), INTERVAL 90 DAY) AND `status` IN(0, 2, 3, 5, 6)