What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » MySQL - Compare 2 tables

MySQL - Compare 2 tables
Author: Message:
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
32 / Male / Flag
Joined: Jul 2006
O.P. MySQL - Compare 2 tables
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?

This post was edited on 10-08-2009 at 02:01 PM by Jimbo.
10-08-2009 01:55 PM
Profile E-Mail PM Find Quote Report
matty
Scripting Guru
*****


Posts: 8336
Reputation: 109
39 / Male / Flag
Joined: Dec 2002
Status: Away
RE: MySQL
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?
10-08-2009 01:58 PM
Profile E-Mail PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
32 / Male / Flag
Joined: Jul 2006
O.P. RE: MySQL
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 ''


What I am looking to compare are the playerupgrade1-4 fields from each record if the difference is > 10.

This post was edited on 10-08-2009 at 02:04 PM by Jimbo.
10-08-2009 01:59 PM
Profile E-Mail PM Find Quote Report
matty
Scripting Guru
*****


Posts: 8336
Reputation: 109
39 / Male / Flag
Joined: Dec 2002
Status: Away
RE: MySQL - Compare 2 tables
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 )
    )


This post was edited on 10-08-2009 at 02:18 PM by matty.
10-08-2009 02:18 PM
Profile E-Mail PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
32 / Male / Flag
Joined: Jul 2006
O.P. RE: MySQL - Compare 2 tables
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


Running MySQL 5.1, if that makes a difference.

This post was edited on 10-08-2009 at 02:20 PM by Jimbo.
10-08-2009 02:19 PM
Profile E-Mail PM Find Quote Report
matty
Scripting Guru
*****


Posts: 8336
Reputation: 109
39 / Male / Flag
Joined: Dec 2002
Status: Away
RE: MySQL - Compare 2 tables
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 )
    )


This post was edited on 10-08-2009 at 03:02 PM by matty.
10-08-2009 03:02 PM
Profile E-Mail PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
32 / Male / Flag
Joined: Jul 2006
O.P. RE: MySQL - Compare 2 tables
That seems to work nicely, thanks a lot :)

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

This post was edited on 10-08-2009 at 03:20 PM by Jimbo.
10-08-2009 03:19 PM
Profile E-Mail PM Find Quote Report
CookieRevised
Elite Member
*****

Avatar

Posts: 15517
Reputation: 173
– / Male / Flag
Joined: Jul 2003
Status: Away
RE: RE: MySQL - Compare 2 tables
quote:
Originally posted by Jimbo
... list all  the differences between integer fields if the difference is > 10...
Meaning you need to check if A is more than 10 bigger than B AND if A is more than 10 smaller than B, correct?. So...
quote:
Originally posted by matty
A.playerupgrade1 > ( B.playerupgrade1 + 10)
That will not list values in B which are at least 10 bigger than A; it will only list values in B which are at least 10 less than A!

Thus the correct clause would then be:
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
)

ABS() = take the absolute value of the number





------------


EDIT:
quote:
Originally posted by Jimbo
Well, the nature of my table, makes it impossible for the record to decrease from B to A
Ah... In that case I never said anything ;)

This post was edited on 10-08-2009 at 08:03 PM by CookieRevised.
.-= A 'frrrrrrrituurrr' for Wacky =-.
10-08-2009 07:21 PM
Profile PM Find Quote Report
Jimbo
Veteran Member
*****

Avatar

Posts: 1650
Reputation: 18
32 / Male / Flag
Joined: Jul 2006
O.P. RE: MySQL - Compare 2 tables
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.
10-08-2009 07:57 PM
Profile E-Mail PM Find Quote Report
matty
Scripting Guru
*****


Posts: 8336
Reputation: 109
39 / Male / Flag
Joined: Dec 2002
Status: Away
RE: MySQL - Compare 2 tables
If you want to speed it up you should use indexs.
10-08-2009 08:09 PM
Profile E-Mail PM Find Quote Report
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On