JOIN - how does it work?
Posted by:
Jiri
Date: May 26, 2006 05:28AM
SELECT * FROM Measure m LEFT JOIN (Relation rld JOIN Object d ON rld.id_a = d.idO AND d.typ = 'dokument') ON rld.id_b = m.idM ORDER BY idM
This query returns in one database result:
(idM value idR id_a id_b idO typ char_a)
32 13021 \N \N \N \N \N \N
33 13021 142 16 33 16 dokument emise z dopravy od roku 2001
34 963852741 145 17 34 17 dokument REZZO 3 od roku 2001
(It's what I've expected.)
But in the other one it returns:
(idM value idR id_a id_b idO typ char_a)
32 13021 139 11 32 NULL NULL NULL
32 13021 144 3 34 NULL NULL NULL
32 13021 142 16 33 NULL NULL NULL
32 13021 140 11 33 NULL NULL NULL
32 13021 145 17 34 NULL NULL NULL
32 13021 143 1 34 NULL NULL NULL
32 13021 141 3 33 NULL NULL NULL
33 13021 145 17 34 NULL NULL NULL
33 13021 143 1 34 NULL NULL NULL
33 13021 141 3 33 NULL NULL NULL
33 13021 139 11 32 NULL NULL NULL
33 13021 144 3 34 NULL NULL NULL
33 13021 142 16 33 16 dokument emise z dopravy od roku 2001
33 13021 140 11 33 NULL NULL NULL
34 963852741 145 17 34 17 dokument REZZO 3 od roku 2001
34 963852741 143 1 34 NULL NULL NULL
34 963852741 141 3 33 NULL NULL NULL
34 963852741 139 11 32 NULL NULL NULL
34 963852741 144 3 34 NULL NULL NULL
34 963852741 142 16 33 NULL NULL NULL
34 963852741 140 11 33 NULL NULL NULL
It's totaly diferent. And I don't undertand why.
Can anybody tell me why?
Thanks
In both databases I have this:
CREATE TABLE `Measure` (
`idM` bigint(20) unsigned NOT NULL auto_increment,
`value` varchar(255) collate cp1250_czech_cs default NULL,
PRIMARY KEY (`idM`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs AUTO_INCREMENT=35;
INSERT INTO `Measure` VALUES
(32, '13021'),
(33, '13021'),
(34, '963852741');
CREATE TABLE `Object` (
`idO` bigint(20) unsigned NOT NULL auto_increment,
`typ` varchar(255) collate cp1250_czech_cs default NULL,
`char_a` varchar(255) collate cp1250_czech_cs default NULL,
PRIMARY KEY (`idO`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs AUTO_INCREMENT=30;
INSERT INTO `Object` VALUES
(1, 'látka', 'SO2'),
(3, 'zdroj', 'REZZO 1'),
(11, 'látka', 'TZL'),
(16, 'dokument', 'emise z dopravy od roku 2001'),
(17, 'dokument', 'REZZO 3 od roku 2001');
CREATE TABLE `Relation` (
`idR` bigint(20) unsigned NOT NULL auto_increment,
`id_a` bigint(20) unsigned NOT NULL default '0',
`id_b` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`idR`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs AUTO_INCREMENT=146 ;
INSERT INTO `Relation` VALUES
(139, 11, 32),
(140, 11, 33),
(141, 3, 33),
(142, 16, 33),
(143, 1, 34),
(144, 3, 34),
(145, 17, 34);
PS:
This query returns first (expected) result in both databases:
SELECT *
FROM Measure m
LEFT JOIN (select rld.id_b, rld.id_a, d.idO from
Relation rld
JOIN Object d ON rld.id_a = d.idO
AND d.typ = 'dokument'
) as rldd ON rldd.id_b = m.idM
ORDER BY idM