View Single Post
centered effect

 
Member Since: Dec 31, 2005
Posts: 24
centered effect is on a distinguished road

centered effect is offline
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`);
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
Result being:
Code:
name
-------
Bill
Tom
Steve

PowerBook G4 15" 1.5Ghz | 2GB PC2700 | 80GB HDD | OSX 10.4.11
QUOTE Thanks