List:General Discussion« Previous MessageNext Message »
From:J B Date:July 14 2011 11:02pm
Subject:Efficient use of sub queries?
View as plain text  
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;




Thanks

Thread
Efficient use of sub queries?J B15 Jul