Help Comparing 2 mySQL tables THANKS!!!

[Guest_imported]

Hi, I've posted this question here before, but none of the suggestions have worked; I'm now thinking that I've finally found something impossible to do with php/mySQL

I have 2 "nearly" identical tables. One table is old, one is new.

The new table has just a few things MORE than the old table has. What kind of query will print to a file, the list of the newly added stuff (red items) in the new table?

Were talking huge tables. In my example below, I want to limit it to only RED items that have been ADDED.

(tables were created as follows)
CREATE TABLE Old_table (
color varchar(20) NOT NULL default '',
item varchar(20) NOT NULL default '',
price decimal(4,2) NOT NULL default '0.00',
PRIMARY KEY (item),
);

Let's say in the "old" table, I have:
red car 2000.00
green truck 1000.00
red apple 2.00
red boat 3000.00

In the "new" table, I have:
green truck 1000.00
purple car 2000.00
red apple 2.00
red boat 3000.00
red blanket 15.00

The exact result I would want to see is:

red blanket 15.00


Thank you!!

 

[sleipnir214]

Did anyone suggest this idea?


Create two more tables:
[tt]

CREATE TABLE Temp_table (
  ncolor VARCHAR(20) NOT NULL DEFAULT '',
  nitem VARCHAR(20) NOT NULL DEFAULT '',
  nprice DECIMAL(6,2) NOT NULL DEFAULT '0.00',
  ocolor VARCHAR(20) NOT NULL DEFAULT '',
  oitem VARCHAR(20) NOT NULL DEFAULT '',
  oprice DECIMAL(6,2) NOT NULL DEFAULT '0.00'
);

CREATE TABLE NewItems_table (
  color VARCHAR(20) NOT NULL DEFAULT '',
  item VARCHAR(20) NOT NULL DEFAULT '',
  price DECIMAL(6,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (color, item)
);

[/tt]


Then perform these three SQL statements:
[tt]

INSERT INTO Temp_table SELECT * FROM New_table n LEFT JOIN Old_table o ON n.color = o.color AND n.item = o.item WHERE n.color = 'red';

INSERT INTO NewItems_table SELECT ncolor, nitem, nprice FROM Temp_table WHERE ocolor = "" AND oitem = "";

DROP TABLE Temp_table;

[/tt]

NewItems_table will now contain the items you're looking for.