What I am trying to do, is query 2 tables, and list all  the differences between integer fields, if the difference is > 10. I have been thinking about ways to do this, and havent really come up with one.

What I would really like to do is create something that prints this to a table, using PHP, giving the old record above, and the new changed record below it. Anyone got any ideas?

Are the table definitions the same? As in each column is the same? Are you trying to do it based on rows? Like does TableA.Row1 = TableB.Row1?

The 2 table definitions are exactly the same, one is just an older backup I have.

Im trying to do it per record, I updated the first post a bit with a better description.

My table structure is something like:

quote:

---

CREATE TABLE `upgrades` (
  `steamid` varchar(20) NOT NULL DEFAULT '',
  `vip` tinyint(1) NOT NULL DEFAULT '0',
  `playerupgrade1` int(5) NOT NULL DEFAULT '1',
  `playerupgrade2` int(5) NOT NULL DEFAULT '1',
  `playerupgrade3` int(5) NOT NULL DEFAULT '1',
  `playerupgrade4` int(5) NOT NULL DEFAULT '1',
  `playerupgrade5` int(5) NOT NULL DEFAULT '1',
  `playerupgrade6` int(5) NOT NULL DEFAULT '1',
  `playerupgrade7` int(5) NOT NULL DEFAULT '1',
  `playerupgrade8` int(5) NOT NULL DEFAULT '1',
  `upgradepoints` int(5) NOT NULL DEFAULT '30',
  `zombieupgradepoints` int(5) NOT NULL DEFAULT '10',
  `bonusupgradepoints` int(5) NOT NULL DEFAULT '0',
  `success` tinyint(1) NOT NULL DEFAULT '1',
  `playermodel` varchar(255) NOT NULL DEFAULT ''

---

Really crappy way of doing it... not sure if it works but give it a shot.

sql code:

---

SELECT * FROM upgrades A
    INNER JOIN upgrades_backup B ON B.steamid = A.steamid
WHERE (
    A.playerupgrade1 > CAST ( B.playerupgrade1 + 10 AS INT )
    OR A.playerupgrade2 > CAST ( B.playerupgrade2 + 10 AS INT )
    OR A.playerupgrade3 > CAST ( B.playerupgrade3 + 10 AS INT )
    OR A.playerupgrade4 > CAST ( B.playerupgrade4 + 10 AS INT )
    )

---

Just tried it:

quote:

---

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT )
    OR A.playerupgrade2 > ( CAST ( B.playerupgrade2 + 10 AS INT )
    OR' at line 4

---

Oops I screwed up

sql code:

---

SELECT * FROM upgrades A
    INNER JOIN upgrades_backup B ON B.steamid = A.steamid
WHERE (
        A.playerupgrade1 > ( B.playerupgrade1 + 10)
        OR A.playerupgrade2 > ( B.playerupgrade2 + 10 )
        OR A.playerupgrade3 > ( B.playerupgrade3 + 10 )
        OR A.playerupgrade4 > ( B.playerupgrade4 + 10 )
    )

---

That seems to work nicely, thanks a lot

Now I just have to wait for it to compare 60,000 records

quote:

---

Originally posted by Jimbo
... list all  the differences between integer fields if the difference is > 10...

---

quote:

---

Originally posted by matty
A.playerupgrade1 > ( B.playerupgrade1 + 10)

---

sql code:

---

...
WHERE (
    ABS(A.playerupgrade1 - B.playerupgrade1) > 10
    OR ABS(A.playerupgrade2 - B.playerupgrade2) > 10
    OR ABS(A.playerupgrade3 - B.playerupgrade3) > 10
    OR ABS(A.playerupgrade4 - B.playerupgrade4) > 10
)

---

quote:

---

Originally posted by Jimbo
Well, the nature of my table, makes it impossible for the record to decrease from B to A

---

Well, the nature of my table, makes it impossible for the record to decrease from B to A, and all I wanted to do was list values in B that are at least 10 less than A. Maybe I should have worded what I required a bit easier, Matty's query seemed to work fine though.

If you want to speed it up you should use indexs.