Q:

Efficient use of sub queries?

I was wondering if any one could point out potential problems with the following query or if there was a better alternative
From a list of users I want to return all who don't have all the specified user_profile options or those who do not have at least one preference set to 1. The following query seems to work fine.
SELECT DISTINCT(c.id) FROM user c
                 WHERE c.id IN (901,907)
                 AND 
        ((SELECT COUNT(id) FROM user_profile
                          WHERE option_id in (747,749) AND user_id=c.id) < 2
         OR
        (SELECT COUNT(id) FROM user_profile
                          WHERE option_id in (747,749) AND user_id=c.id AND preference = 1) != 1
         )

The table definition
CREATE TABLE `user_profile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `preference` decimal(10,2) DEFAULT '0.00',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_profile_user_id` (`user_id`),
  KEY `user_profile_option_id` (`option_id`),
  CONSTRAINT `user_profile_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_profile_option_id` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`),
) ENGINE=InnoDB;

A:

Join the 2 predicates into a single query, which will perform a single table lookup.
SELECT c.id
FROM user c LEFT JOIN user_profile p 
     ON c.id = p.user_id
     AND p.option_id in (747, 749)
WHERE user_id IN (901,907)
GROUP BY c.id
HAVING COUNT(p.id) < 2 OR SUM(p.preference = 1) != 1

