I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be "5" which is probably wrong.
The expected Result ist "4".
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
--
----------------------------------------------------------------
Stephan Skusa mailto:stephan.skusa@;lippe-net.de
Lippe-Net Online-Service http://www.lippe-net.de
Herforder Strasse 309 tel.: +49 (0)521 - 977 998 - 0
33609 Bielefeld - Germany fax: +49 (0)521 - 977 998 - 9
----------------------------------------------------------------
---------------------------------------------------------------------
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
