In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!
SELECT * FROM MyTable AS T1
LEFT JOIN MyTable AS T2 ON T1.productID=T2.productID
WHERE 1
AND T1.ListNr=1
AND T2.ListNr=2
AND T1.productID IS NOT NULL
AND T2.productID IS NULL
;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
-- Table structure for table `test`
--
CREATE TABLE IF NOT EXISTS `test` (
`ListNr` varchar(11) NOT NULL,
`productID` varchar(256) NOT NULL,
`PRICE` decimal(11,8) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
INSERT INTO `test` (`ListNr`, `productID`, `PRICE`) VALUES
('TTT', '1234', '1.23000000'),
('TTT', '3456', '2.45000000'),
('TTT', '6789', '6.78000000'),
('TTT', '8765', '9.01000000'),
('ZZZ', '1234', '1.23000000'),
('ZZZ', '3456', '2.45000000'),
('ZZZ', '6789', '6.78000000'),
('ZZZ', '4321', '7.89000000'),
('ZZZ', '8765', '9.02000000'),
('ZZZ', '9999', '9.00000000');
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
Open in new window