MYSQL :: Compare Two Tables

Compare Two Tables
I have two tables.

First table is just list of dealers (table name dealers)

Second table is user input of dealers transactions.(Table name is log) The first field of table two is the dealership name (field name is dealership).

Sometime not all dealers in the first table perform transactions.

How can show the dealers that are listed in the first table that are not listed in the second table.

I tried this, but it did not work

select dealers from dealers, log where dealers != dealership

How To Compare 2 Tables ?
I have two tables containing references and I would like to compute the differences but I have some problems. For example

table 1

+--------+
| ref |
+--------+
| A250 |
| B260 |
| G650 |
| D250 |
+--------+

table 2

+--------+
| ref |
+--------+
| A250 |
| B280 |
| Z650 |
| D250 |
| W650 |
+--------+

SELECT table1.`ref` FROM table1, table2 WHERE table1.`ref` NOT LIKE table2.`ref` group by table1.`ref` ;

does not compute corectly the diffÃ©rences.

On the other end :

SELECT table1.`ref` FROM table1, table2 WHERE table1.`ref` LIKE table2.`ref` group by table1.`ref` ;

computes correctly the comon references.

So what is wrong in the first query ? What can I do to see the differences between my 2 tables ?.

How Do I Compare Two Tables?
I have two tables with the same columns.Table A and Table B. I would like to compare a column in bot tables and determine if there is an entry in A but not in B, also I'd like to determin if there is an entry in B but not in A. How would I do this?

Compare Tables
I am looking to figure out a way to compare two tables and show me what is different.
One table is 'usertable'.This table contains 5 columns reference(key),tag,datatype,scantime,description. When I run my application,it takes a snapshoot of 'usertable' and create a temp table 'temptags'.Every couple of minutes I want to compare the two tables and determine what is different. Fist off,I want to compare the reference column of each table and determine if there is a new reference index or if any have been deleted.Then I want to look at all the other column and determin if any have changed.

These two tables are located in different databases 'usertable' is located in 'user' database and 'temptags' is located in 'tags' database.

Compare Two Tables
there is a way to compare fields and rows in two tables and display the values that differ from each other.

Example: I have a table called "products", and a table called "products_bak", with a few fields: id, name, quantity. "Products_bak" contains yesterdays values, and "products" contains todays updated values.

"Products_bak.quantity" contains yesterday's stock, and "products.quantity" contains current stock. Is there a way to compare the two tables and get the differences. I would like to be notified that the stock has increased from 3 to 5 in the first row, and that the name has changed from "TestOld" to "TestNew".

Can't Compare 2 Tables' Columns
For some reason, comparing 2 tables using the operator "<>" is not working but using the opposite operator "=" is returning the correct opposite results.

I have 2 tables:
wp_posts with a column called 'post_title'
sas_products with a column called 'sas_product_id'

Both 'post_title' and 'sas_product_id' contain the same type of data, a product ID.

So if I do the opposite query from what I want, it works:

SELECT ID
FROM wp_posts, sas_products
WHERE post_title = sas_product_id
Results give me the 100 rows where the post_title and sas_product_id are equal.

But, if I do the following query, I get 10,000+ results when I should just get 2.

SELECT ID
FROM wp_posts, sas_products
WHERE post_title <> sas_product_id
Can someone help explain what it is I am missing here?

Compare 2 Tables And Delete
i made a rookie mistake in my delete user code and now i got a problem that i still got challenges by the user in my database with the deleted user_id

i have two tables set like so

Table users contains one cell called user_id
Table challenges contains two cells called challenge_to and challenge from - each contain a user_id

i need to get all the user_id `s from the users table and then delete all challenges that contain a user_id not in the list

Compare Fields Tables
I would like to compare two tables, in both tables there is an user field called "user" (table_1.user, and table_2.user). Thee is however a small problem, in table_1 the users are inserted like this: 235463 and in table_2 the users where inserted like this: p235463!

Would it still be possible to compare this fields in some way (By using only the numbers maybe? But how?)

Compare 2 Tables ( <> Operation )
I have a small problem in comparing two tables, that has to return records that are not in the other table.

Suppose I have the following tables:

T1:               T2:
Field1            Field1
------            -------
1                  b
2                  a
d                  c
a                  1
x
z

And, this is the code:

select T1.Field1 from T1, T2
where T1.Field1 <> T2.Field1
group by T1.Field1

I was expecting the result of 2, d, x, z (which are not in T2). Instead, I got all 6 records from T1. I've tried other operation like !=, NOT IN, but came up the same. The Field1 on T1 is primary key.

Compare And Get Result From Two Tables
I'm trying to compare the id from the users table to the id of the white_player in the games table, and provide me with the users name. I think I am close, but unsure of how to actually get the result. Pretty sure the query is good, but not sure what the second line should look like.

$wplayer = mysql_fetch_object(mysql_query("SELECT ".$this->settings->mysql_prefix."users.nick," .$this->settings->mysql_prefix."users.id," .$this->settings->mysql_prefix."game.white_player from ".$this->settings->mysql_prefix."users ".$this->settings->mysql_prefix."game WHERE " .$this->settings->mysql_prefix."users.id = " .$this->settings->mysql_prefix."game.white_player "));
$wplayer = $wplayer->nick;

Compare Record In Two Tables
if there is an easy way to compare a row from table
a, to a row in table b with the same structure/field names and return
those fields which don't match?I have table_a storing the originally submitted applicant information,and table_b storing a version that can be changed. When a user makes a
change I'd like to compare it to the original table to see what fields
were changed, preferably without opening the row from both tables and
going field by field in code to see if they match...

How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!".

I have 2 tables, same DB.

Table 1 is named Policies
Table 2 is named Assignments

Both contain a PolicyID field.

I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments.

So if "Select PolicyID from Assignments" returned the following:
1, 2, 3, 4, 5, 6

And "Select PolicyID from Policies" returned:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10

What do I use to return this? 7, 8, 9, 10

Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!

Compare 2 Tables And Update One If A Match.
I have two tables "contact_tbl" and "address_tbl". I have a query that compares the email addresses between the two. If a match is found I need to update a specific field in "contact_tbl" with a value of 'yes'. My query is:

SELECT * FROM contact_tbl
INNER JOIN address_tbl
ON contact_tbl.email = address_tbl.email;

This works and I get a nice result set showing records where the email addresses match. I now need to update a field "status" in contact_tbl when there is a match. Have been tinkering with this for a while but not sure of the syntax and placement for the UPDATE code.

Compare And Delete Or Insert Within Tables
I have a CSV file that contains addresses. I need to compare the csv file to a MySQL table (tbl_bl).

Then if the addresses are not found in tbl_bl, I need to add the addresses to another table (tbl_wl) Only if they do not already exist in the tbl_wl table. I can create simple SQL queries but I'm not so sure about something this complex.

Compare Data Beetwen 2 Tables
MYSQL problem:

I'm trying to copy all unique data from a heap table
to a myisam table:
CREATE TABLE `heap_in` (
`id` varchar(32) NOT NULL default '',
`domain` varchar(50) NOT NULL default '',
) TYPE=HEAP;

CREATE TABLE `last_visits` (
`id` varchar(32) NOT NULL default '',
`domain` varchar(50) NOT NULL default '',
KEY `id` (`id`,`domain`)
) TYPE=MyISAM;

Heap table just record the data. heap_in.id and heap_in.domain are not unique.
While I'm inserting data to myisam table I want to insert just one time the same domain(if it is duplicated) for the same id. Code:

Compare 2 Tables And Find Rows Which Don't Match (was "Mysql Noob Question")
I have a products table and a products_description table. They both have products_id as primary key. However, products_description table has more products_id keys then the products table. How do i compare the 2 tables and show just the products_id that dont match from products_descripition table?

SELECT products.products_id, products_description.products_id FROM products, products_description WHERE products.products_id != products_description.products_id;

I tried that query but got some wierd results and also the results gave me 2 columns when I just want one.

Compare Row 1 With Row 2
I have a table with data that is supposed to only increase in value, but due to bugs outside my control, I need to check the entries in the database on a periodic basis.

I can of course do this in a scripting language like php but I was wondering if there is a way to do it in MySQL with some kind of SELECT query reducing the server load?

the table looks like:
id auto_increment
timestamp
data (numeric value that will increase with time)

What I'm looking for is something like this
SELECT id FROM table WHERE data is less than previous row's data ORDER BY timestamp

Compare
How can I stop a function in PHP?

When comparing data I want to stop the function on a hit.

Function compare($data) {

For ($n=1; $n<=10000; $n++) {

For ($m=1; $m<=10000; $m++) {

If $data[$n]==data[$m] {

How To Compare Different Datatypes?
how to create user except root account ?

Compare Dates
Using mysql 4.0, which doesn't support DATEDIFF

I'd like to compare two dates and check if the difference is 6 weeks or greater. How can I do this?

Compare 2 Databases
I have 2 CMS Systems running on MySql
I was upgrading one of them.
During the update(s), i messed some fields-defs at some tables.

Is there any possibilities to compare those two databases to see, which Field-Attributes are different (Just Name, Type) and which Tables are different (has table1 on DB1 other fields than table1 on DB2)

Compare Strings In SQL
I have a string field where are stored dates and i want to select from this table like this:

"SELECT * FROM [table] WHERE field_date>='date1' AND field_date<='date2'".

Apparently doens't work, the query returning all the rows in the table. Is there a posibility for this string comparison or i should probably convert the field that stores these dates into the date one?

Compare Schema
I have two MySQL database. How can I compare the schema. I wan't to see if there are any differences in them. Is there a tool for this?

Date Compare
I need to compare dates without the time. In oracle, I would use a trunc() function to drop the time. How is this done in MySQL?

LIKE Compare Two Columns
I have a issue comparing two columns.

I am using a select query, and here it is:

SELECT project_name AS project, project_manager AS manager, percent_complete AS completed, plcp_phase AS phase, (

SELECT COUNT( * ) FROM meetings WHERE project_name = project) AS meetings,

(SELECT COUNT( * ) FROM filesdocs WHERE product LIKE project) AS files,

(SELECT COUNT( * ) FROM issues WHERE project_name = project) AS issues

FROM projects GROUP BY project");

I have several tables, projects, whehere i obtain the project name, filesdocs, where i am looking for the amount of files for each project, and the same with issues and meetings.

In filesdocs, product name could be "xxx", or "yyy", or any other project name, OR "xxx yyy" (with a space), becuase that file is relavant for TWO projects. Which would mean if i LIKE seach for "%xxx%", then the files with the products named "xxx" AND "xxx yyy" will get returned. Works great if i supply the project name as variable, since i can quote the variable as "%variable%".

However, in the search above where i the seach finds all the different projects in one search, how can i do a LIKE seach with %'s on the column alias 'project'? Although the alias "project" LIKE product works, it does not return the count of files where products is in the form of "xxx yyy", as obviously if the current project in the sql search = "xxx", then "xxx" obviously does NOT equal "xxx yyy" and the count misses a file. But i cannot find a way of using the alias with % % marks encapsulating it. Code:

Compare Data
I want to compare data in a single table, and get the index number as well (if these columns have same data/or under the condition i setup).

example:

table:
index ---- col1
1 ---- 260
2 ---- 240
3 ---- 330

I would like to compare these col1 data and return the number(s) which is the largest (the other case is +/- 30 of the smallest). And return the index of the number(s) too.

Compare Two Date
i have a table with txn_id, txn_name,txn__date.

now i want get the all txn_id's whose txn_date is in between 01-01-01 and 02-02-02.

Compare Two Datasets
I have two tables, TableA and TableB.

TableB holds a copy of some records in TableA. TableB is read only.

TableA is live--rows are added, deleted, and updated regularly.

I need to write a script to see if rows from TableA which correspond to rows that exist in TableB (by corresponding ID field) have been modified, and if they have, I need to copy the new version of the row from TableA to TableB.

My first guess was to do some sort of LEFT JOIN WHERE NULLs existed after comparing each column for exact match. Ugly.

My second guess was to do:

INSERT INTO TableB ([cols])
SELECT * FROM TableA WHERE NOT EXISTS (SELECT * FROM TableB WHERE col1=TableA.col1, col2=TableA.col2, etc.).

Compare Two Dates
I have a field in my database called rntl_date and this contain the date when the apartment will be available.
I want to compare this with Curdate() and if this is less then Curdate() to show Availabe
For example:

rntl_data curdate()
2004-12-12 2005-10-10 ->Available
2006-12-12 2005-10-10 ->Non available

Compare Date
The way the current script was written was to write the date in a varchar field in the format 2008-02-12 18:22:15

I wish to take the number days before expiry for the expiry column and compare it.
Is that possible in a field that was not formated for date?
If date+expiry = today then run this other script.

Compare Values
What I need is a mysql query where when given a value, a message will be returned if the value in the table is plus or minus 15 from the new value that is being entered. Sort of comparing the 2 but the new value is going into a different table. I do not even know where to begin!
Such as

select weight from 'table' if $weight is + or - 15

Compare Two Fields
I have a simple table that I need to compare the TL fileds and produce a report only if the TL fields matches and the Status feild equals Buy and Sell.

I want to match any inventory that is the same TL and I am buying, selling, and report that from my database. I am copying a simplified version of the table as an example.

I am using PHP and MySQL as tools and cannot figure out how to comapire all the fields, as I am a rookie at best.

IDRefNoTLStatusStatus_Date
5625DryVanSell2006-06-21
5725DryVanBuy2006-06-30

Compare 2 Databases
How can I compare 2 databases to check if one is embedded within the other . That mean given a database A , add some tables and new records to it , modify some tables , that result in table B . How can I check that A is embedded with B ? that all A entries and part of B ? . Any easy tools to do that ?

Compare Datetimes
How do you compare two datetimes to find out which is earlier or later? I've tried the following (log.time_added is a datetime):Code:

...where log.time_added >= FROM_UNIXTIME( 1163656800 )
AND log.time_added <= FROM_UNIXTIME( 1163743200 )

and it returns all results from any date, including dates not in the specified range. I have confirmed that FROM_UNIXTIME is working correctly, it just isn't comparing the dates correctly.

Extract And Compare
I was requested to do a report which consists in:
Report total sales (for example) in month: XX
Now, The user has 2 options:
- Actual month (default)
- A month choosen from (month name? or month number?)
I'd like you to help me in these 2 steps...
1. How to get records which were "saved" in actual month ?
2. How to compare and make queries if user selects month name or month number ?

Compare New Data
I have a bunch of feeds and I was wondering how I would compare the old one with the new one and then report the difference? For example, one item I would like to see is if changes were made to a products price like in the example below

Old Data

1 Blah Blah item image.jpg description $24.99
2 New Example pictur.jpg summary $99.99

New Data

1 Blah Blah item image.jpg description $14.99
2 New Example pictur.jpg summary $99.99

Since #1's price was changed, after I upload the information I would like it to say item one's price was change by $10 and is now $14.99. How would I do this or what technique would I use?

Compare MySql And MsSql
I am trying to switch from Microsoft MSSQL to the free mySql. But I am going to explain to customer the impact.

I want to know the following stuff.
1) What MsSQL can do but MySql cannot do?
2) What is the number of connections supported by each product.
3) Where (URL) can I find more information about the above issue?

Compare Data By Similarity
I have datum which contains m values (v1, v2, ..., vm) and I would like get a sorted list of such data so that it is sorted by similarity to some given datum k with values (k1, k2, ..., km). Similarity means in this case how many values are the same. Some datum is more similar to the k than some other one if it has more equal values (v1==k1, v2==k2, ... pairs) that the other one.

I made a table Data with (DataID, ValueID, Value) and try this select query to get a list compared to the first datum:

SELECT Data1.DataID, COUNT(*) AS C FROM Data AS Data1, Data AS Data2 WHERE Data2.DataID=0 AND Data2.ValueID=Data1.ValueID AND Data2.Value=Data1.Value GROUP BY Data1.DataID ORDER BY C DESC;

But it is really slow for large data sets.

Is there some better way to store this data into MySQL database and than retrive sorted list?

Possible To Compare `field_1` NOT LIKE `%field_2%` ?
We have two fields which contain different descriptions -- field_2 is supposed to be a subset of field_1, so they will not be "equal." I have not found a way to do this by experimentation or in documentation. We have both MySQL 4.1 and 5 available.

Compare Two Different Date Format
Take a look into the following select statements:

Select Date_Format(TS_Post_DT,"on %e/%c/%Y at %T"), UI_Login FROM Topic_Start LEFT JOIN User_Info ON TS_UI_ID=UI_ID WHERE TS_D_ID='.$row['D_ID'] .' LIMIT 1'

SELECT Date_Format(User_Option.UO_Last_Login,'on %e/%c/%Y at %T') FROM User_Option LEFT JOIN User_Info ON User_Option.UA_UI_ID=User_Info.UI_ID WHERE User_Option.UA_UI_ID=".$_SESSION['id'];

I want to compare the two Date_Format fields.....how can i do this thing?

How i can print the row Date_Format(User_Option.UO_Last_Login,'on %e/%c/%Y at %T') after the execution of the query......I used the row[0]

DATETIME Compare / Subtraction
I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows.

What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds).

Compare DATETIME With DATE
Is there a possibility to compare DATETIME with DATE? It's because I have a table with DATETIME columns but at one point i need to filter the DATETIME entries according to a certain DATE.

Unique Key To Compare Entire Row
I need MySQL to recognize duplicate rows in their entirety (minus auto-incrementing id field).

For example if record exists:
id col1 col2 col3
1 info1 desc1 cont1
2 info2 desc2 cont2 *
3 info3 desc3 cont3
4 info2 desc2 cont2 *
5 info1 desc2 desc3

So it should allow the import of all rows except row 4, because it is an exact duplicate of row 2 (except the id field). It should SAVE row 5 because it has not unique data, but a unique collection of data.

How can I achieve this goal using either unique keys or some savvy scripting?

Compare IP Address And Subnet?
I am trying to use mysql to do the following thing. I have tableA, in which all are single ips.

sip
-------
1.2.3.4
8.8.8.8
123.3.32.1
....

and tableB, a list of our group subnets.

subnet
---------
1.0.0.0/8
20.0.1.0/24
.....

What I want is only picking up those ips belong to our group from tableA according to the subnet tableB I can't figure out a way to compare ip and subnet, any ideas?

Compare Date Problem
In a table, i have two date fields (startDate, endDate)

I want to make a query that checks if two date that i entered in a form (date1,date2) is between the two dates dates in my table.

date1 = 2006-08-01
date2 = 2006-08-31

I thought something like this would work but it doesnt... can anybidy help me?

SELECT typeFormation, startDate, endDate FROM formations WHERE (date1>=startDate AND date1 <= endDate) OR (date2>=startDate AND date2 <= endDate)

Compare Difference Between Dataset
My mysql database will take in a larget set of data periodically. Each set of data has a unique ID that I can control. I'm looking for a simple way to display the difference between each set of data. The data is in the following format

SET 1
ip port runid
192.168.0.1 20 1
192.168.0.1 23 1
192.168.0.2 20 1

SET 2
ip port runid
192.168.0.1 20 2
192.168.0.2 20 2

The difference is that one of the IP in SET 1 is gone in SET 2. This is the type of difference that I'm looking for.

Can anyone tell me what's the simplest way to generate a differential report? I was thinking about putting each set of data in their own seperate databases(9 tables in each set, but only 1 needs to be compared), then run some kind of command that compare databases.

DATETIME Compare/subtraction
--0-930715088-1061086093=:22602
Content-Type: text/plain; charset=us-ascii

I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds).

Compare Two Tables

How can I compare two tables and receive only the entry that are not the same as the first table. Example: First Table (fruit); contain apple, orange, lemon and lime. Second table (remove); orange, lime. I need to have only apple and lemon as the result of my SQL SELECT.

How can I compare two tables and receive only the entry that are not the same as the first table.

Example:

First Table (fruit); contain apple, orange, lemon and lime. Second table (remove); orange, lime.
I need to have only apple and lemon as the result of my SQL SELECT.