Re: inner join
poupi 2 poupi my solution is
SELECT TmpA.id,TmpA.pcode, IFNULL(testA.pvalue,'') as pvalue
FROM
(
SELECT testA.id, testB.pcode,testA.pvalue,testB.pstate
FROM testA CROSS JOIN testB
ORDER BY testA.id
) as TmpA
LEFT OUTER JOIN testA ON TmpA.pcode=testA.pcode AND TmpA.id=testA.id
WHERE TmpA.pstate=1
GROUP BY TmpA.id, TmpA.pcode, testA.pvalue
ORDER BY TmpA.id
--
-- Table structure for table `testA`
--
CREATE TABLE `testA` (
`id` varchar(6) collate utf8_unicode_ci NOT NULL default '',
`pcode` varchar(6) collate utf8_unicode_ci NOT NULL default '',
`pvalue` varchar(6) collate utf8_unicode_ci NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `testA`
--
INSERT INTO `testA` VALUES ('u50001', 'P00001', 'nom');
INSERT INTO `testA` VALUES ('u50001', 'P00002', 'prénom');
INSERT INTO `testA` VALUES ('u50001', 'P00003', 'rue');
INSERT INTO `testA` VALUES ('u50001', 'P00004', 'ville');
INSERT INTO `testA` VALUES ('u50002', 'P00001', 'nom');
INSERT INTO `testA` VALUES ('u50002', 'P00002', 'prénom');
INSERT INTO `testA` VALUES ('u50002', 'P00003', 'rue');
INSERT INTO `testA` VALUES ('u50002', 'P00004', 'ville');
INSERT INTO `testA` VALUES ('u50003', 'P00001', 'nom');
INSERT INTO `testA` VALUES ('u50003', 'P00003', 'rue');
INSERT INTO `testA` VALUES ('u50003', 'P00004', 'ville');
--
-- Table structure for table `testB`
--
CREATE TABLE `testB` (
`pcode` varchar(6) collate utf8_unicode_ci NOT NULL default '',
`pstate` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `testB`
--
INSERT INTO `testB` VALUES ('P00002', 1);
INSERT INTO `testB` VALUES ('P00003', 1);
INSERT INTO `testB` VALUES ('P00004', 0);
INSERT INTO `testB` VALUES ('P00001', 0);
INSERT INTO `testB` VALUES ('P00005', 1);
but if someone has something better ?
Regards