Forums
New posts
Articles
Product Reviews
Policies
FAQ
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Menu
Log in
Register
Install the app
Install
Forums
Digital Lifestyle
Web Design and Hosting
SQL: HAVING question
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="centered effect" data-source="post: 797658" data-attributes="member: 14713"><p>xstep, at first I thought nice solution but then why not go simpler?</p><p></p><p>I hope I have the tables right:</p><p>[CODE]CREATE TABLE `A` (</p><p> `id` tinyint(2) NOT NULL auto_increment,</p><p> `name` varchar(50) NOT NULL,</p><p> PRIMARY KEY (`id`)</p><p>) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;</p><p></p><p>INSERT INTO `A` VALUES(1, 'Bill');</p><p>INSERT INTO `A` VALUES(2, 'Tom');</p><p>INSERT INTO `A` VALUES(3, 'Steve');</p><p></p><p>CREATE TABLE `B` (</p><p> `id` tinyint(2) NOT NULL,</p><p> `status` varchar(100) NOT NULL,</p><p> KEY `id` (`id`)</p><p>) ENGINE=InnoDB DEFAULT CHARSET=latin1;</p><p></p><p>INSERT INTO `B` VALUES(1, 'Y');</p><p>INSERT INTO `B` VALUES(2, 'Y');</p><p>INSERT INTO `B` VALUES(2, 'M');</p><p>INSERT INTO `B` VALUES(2, 'N');</p><p>INSERT INTO `B` VALUES(3, 'N');</p><p>INSERT INTO `B` VALUES(3, 'N');</p><p>INSERT INTO `B` VALUES(3, 'Y');</p><p></p><p>ALTER TABLE `B`</p><p> ADD CONSTRAINT `B_ibfk_1` FOREIGN KEY (`id`) REFERENCES `A` (`id`);</p><p>[/CODE]</p><p></p><p>Now, why not do this:</p><p>[CODE]</p><p>SELECT name</p><p>FROM A</p><p>LEFT OUTER JOIN B ON B.id = A.id</p><p>WHERE B.status != 'M'</p><p>AND B.status = 'Y'</p><p>GROUP BY A.name</p><p>[/CODE]</p><p></p><p>Result being:</p><p>[CODE]</p><p>name</p><p>-------</p><p>Bill</p><p>Tom</p><p>Steve</p><p>[/CODE]</p></blockquote><p></p>
[QUOTE="centered effect, post: 797658, member: 14713"] xstep, at first I thought nice solution but then why not go simpler? I hope I have the tables right: [CODE]CREATE TABLE `A` ( `id` tinyint(2) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; INSERT INTO `A` VALUES(1, 'Bill'); INSERT INTO `A` VALUES(2, 'Tom'); INSERT INTO `A` VALUES(3, 'Steve'); CREATE TABLE `B` ( `id` tinyint(2) NOT NULL, `status` varchar(100) NOT NULL, KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `B` VALUES(1, 'Y'); INSERT INTO `B` VALUES(2, 'Y'); INSERT INTO `B` VALUES(2, 'M'); INSERT INTO `B` VALUES(2, 'N'); INSERT INTO `B` VALUES(3, 'N'); INSERT INTO `B` VALUES(3, 'N'); INSERT INTO `B` VALUES(3, 'Y'); ALTER TABLE `B` ADD CONSTRAINT `B_ibfk_1` FOREIGN KEY (`id`) REFERENCES `A` (`id`); [/CODE] Now, why not do this: [CODE] SELECT name FROM A LEFT OUTER JOIN B ON B.id = A.id WHERE B.status != 'M' AND B.status = 'Y' GROUP BY A.name [/CODE] Result being: [CODE] name ------- Bill Tom Steve [/CODE] [/QUOTE]
Verification
Post reply
Forums
Digital Lifestyle
Web Design and Hosting
SQL: HAVING question
Top