MySQL Forums
Forum List  »  General

Re: inner join
Posted by: poupi poupi
Date: February 07, 2005 05:40AM

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

Options: ReplyQuote


Subject
Written By
Posted
February 04, 2005 11:52AM
Re: inner join
February 07, 2005 05:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.