Same result, also if I do not define unique index.
Just a suggestion: SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND not(pid_segment.id = hl7incom.id) GROUP BY hl7incom.id; Otherwise my only other suggestion would involve using the 'NOT IN' logic, but I think that might be too convoluted for your needs. No way, I've already tried this. > I have two tables > > CREATE TABLE pid_segment ( > id INT NOT NULL UNIQUE PRIMARY KEY, > msg TEXT) > TYPE=INNODB > > CREATE TABLE hl7incom( > id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY REFERENCES pid_segment > (id). > msg TEXT, > time TIMESTAMP NOT NULL) > TYPE=INNODB > > There are few data stored in both tables. Now I will select new messages > from hl7incom, where hl7incom.id is not equal pid_segment.id and store this > id, msg in pid_segment. > > With query... > > SELECT hl7incom.id > FROM hl7incom, pid_segment > WHERE hl7incom.msg LIKE '%PID%' > AND pid_segment.id != hl7incom.id > GROUP BY hl7incom.id; > Try SELECT hl7incom.id FROM hl7incom, pid_segment WHERE hl7incom.msg LIKE '%PID%' AND pid_segment.id<>hl7incom.id GROUP BY hl7incom.id; John Coder --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php