Query to compare data sets

Not completely clear but if I get it right, you're looking for this:

(assume you have a MyTable with a field ListNr that's 1 for the first recordset and 2 for the second; there are some kind of products in the table and they're identified by a productID...  then you can adapt this to your actual situation and queries)

So, the trick is to join the same table to itself:

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
;

Select all Open in new window

Wonder if this helps and the logic is clear.  I didn't test this example as it's time consuming to build the necessary tables but it illustrates the way to go.

I made a table using those field names and ran your query and it returned an empty set.

Yes because it's only the way to do it.
But if you give me an sql export I'll try to tell what's missing.

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');

Select all Open in new window

Okay, now my query tried to find all the rows missing from list 2, existing in list 1 only. There are no such rows as I see. You need several queries for several kinds of differences, like "in list1 but not in list2" (this is what you have now), "in list2 but not in list1" (this would return a nonzero result; swap t1/t2 in my last 2 lines in query), and "in list1 and in list2 but not the same data in other fields", which I have not implemented in detail but it's quite obvious.

I ran both:

SELECT * FROM test AS T1
   LEFT JOIN test AS T2 ON T1.productID=T2.productID
   WHERE 1
   AND T1.ListNr='ZZZ'
   AND T2.ListNr='TTT'

   AND T1.productID IS NOT NULL
   AND T2.productID IS NULL

and

SELECT * FROM test AS T1
   LEFT JOIN test AS T2 ON T1.productID=T2.productID
   WHERE 1
   AND T1.ListNr='TTT'
   AND T2.ListNr='ZZZ'
   AND T1.productID IS NOT NULL
   AND T2.productID IS NULL

Both returned nothing.  Am I misunderstanding you?

No, sorry, I'm telling the wrong thing.
You can't do this left join thing with both lists in one table because join will always find the product in the same list and say "hey, I've got it".  Won't be NULL.  Damn thing.

Okay then.
Shall we use temporary tables or solve the problem in PHP?

TMP tables will make this join possible but you must do ay INSERT ... SELECT for each comparison which is rarely wise.  With PHP you'll have to fetch both record sets and do the comparison in the result arrays - which is easy unless you have millions of records.

OR, maybe someone else has a better solution.

Which way to go?

Yea I'm on the verge of the PHP solution which I can do but I hoped to make something I could distribute easily.

Okay, sorry again.

No need to apologize I appreciate the help.

THanks that seems to do the trick.  I can't get it to work on my large DB for some reason but that is probably my fault.  What would be an optimization for that query?

Also, would you know how to get changed prices?

For optimalization, I have to see, how many records you have in the table and the count of those categories like 'TTT' and 'ZZZ'. But now is the query ok for less than a milion records.