MYSQL :: Compare Fields Tables

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

Is There A MySQL Action To Compare 2 Fields
imagine I have a table with these fields:

id
member_1
member_2

Is it possible to do a query where I want member_1 != member_2 .... is there a MySQL command for this?

How Can I Compare Two Table Fields In One Query?
This is my problem:

"SELECT * FROM a,b WHERE a.field LIKE b.field " --> this works

"SELECT * FROM a,b WHERE a.field LIKE %b.field% " --> this works not

My Question:

Is it possible to compare two filds with the substitute symbol % if the fields are stored in different tables?

Compare Text Fields Of Two Records?
If SELECT DISTINCT FieldName will cull the identical records of a certain field and show a list of the unique values, is there a way to go a step further and indicate what the differences are in that field between any two specified unique records? I am looking for a way to look at blocks of text (articles undergoing editing) up to 2000 words to see what has been added or removed from one record to the next.

Is this possible? If not in MySQL, I can work with VBScript or VB6 if you know of a method for those.

Compare Tables
Does anyone know if there is any tool that can compare two databases (and the tables) and allow you to create the SQL necessary to make them identical? I.e. creating new tables, adding columns,

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".

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.

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 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:

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

Maximum Allowed Tables/db, Fields/tables, Records/table
1)tables that can be created in a MySql database.
2)fields that can be created in a MySql table.
3)records allowed in a MySql table and in a MySql database.
4) allowed joins in a MySql table/database.

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.

Printing Out Tables Without Fields Name
I am using the command:

select address,goto from alias where active=1;

to get a list of e-mail redirections such as:

address goto
job1@example.com user1@domain.tld,user2@domain.tld
job2@example.com usere54@domain2.tld,sfjh@domain32.tld,...
...

Is there a way to get rid of the names of the fields (address and goto), so that the output would only contain the data?

Multiplying Fields In Different Tables
I posted a thread here and got an excellent answer to part of my problem:

http://www.phpfreaks.com/forums/index.php?showtopic=63746

The code works fine. But I need to add something to that. You will see in the third message down that I want to multiply the counts by a value in another table. But I'm not sure how to do it!

As I see it, I need some way to firstly identify the correct price, which is determined by the position number, and then multiply that by the count value.

Selecting Fields From 3 Tables
i have 3 tables.

table 1 - products: [Code, typeCode, supplierCode]
table 2 - suppliers: [code, name]
table 3 - types: [code, name]

how can i select the fields from the 3 tables, meaning where products.typeCpde = types.code, products.supplierCode = suppliers.code

Loading Text Fields Into Tables
Let's say a tab delimited file like this:

name is varchar (60)
bio is TEXT data type

name<tab>bio
Jack<tab>name: My name is Jack
car: I have a chevy car
pet: My pet's
named Fido

Jane<tab>name: My name's Jane
car: I have a VW car
pet: I don't have a
pet

.....

How do I LOAD that into MYSQL ? I want name field and the a text bio field but how do I get it in a table assuming I have line breaks...I'm willing to globally edit the
file if needed....

NOT NULL Fields In INNODB Tables
My database is composed of INNODB tables such as...

CREATE TABLEuser
(
nameVARCHAR(255)NOT NULL

) TYPE = INNODB;

The NOT NULL qualifier doesn't work as I would expect. While it
doesn't allow me to do this:

insert user values (null);

It will let me do this:

insert user values ("");

I appreciate that one could argue that "" is different to NULL and
therefore there is nothing wrong with this behaviour. However, surely
there is no defending the following (which is also allowed):

update user set name=NULL;

Is there any way that I can prevent users from setting a field to NULL
or ""? I am running version 4.0.17-nt on Windows XP

How To Delete Fields From Different Tables With A Single Query
How to delete fields from different tables with a single query?

Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and
forename from a tbl of users (they are always distinct) where user_id
in that tbl equals friend_id in another table friend_id, which 'should'
be unique ie there 'should' only be one row of a 'friend connection',
but in the case of bugs etc this might not be true, what i want to do
is select only the row of tblusers with the user details that are
matched by join condition below, but only ONCE for each, ie not in as
many times as there are the same connection in tblfriends.

For some reason the result i am getting is a repitition of the 2 fields
in tblusers (that i am selecting) for as many rows as there are in
tblfriends that match the join condition.

How can i make it only return a row once?

Database Access Speed: More Tables Or More Fields In A Table?
I have a question: it is better to create a database where there are more tables or more fields in few tables? What I want is: high speed accessing, indexing and searching. So what do you suggest me?

Sorting By Same-data Fields From Multiple Tables *without* UNION
Is there anyway I can sort by two columns (which are the same datatype) from separate tables (whether selected via Table1, Table2... or by LEFT JOIN) without using UNION?

Troubles Into Importing Data Into Tables With Not Null Fields/keys
I have created a table with some fields set as "not null" and one like record key.

The problem I have is that when I try to import data into the table from a txt file using the command "LOAD DATA INFILE..." it is importing "wrong" or "incomplete" records too

My source data is not always correct and some necessary fields could be not available in the text file.

I was supposing that when a table key value was missing or a null value was entered in a not null field, the entire record should be skipped but it is not so: I can see records where the key is missing or where not null fields are inserted like null.

MyOLEDB Doesn't Support Commands Like SHOW FIELDS, SHOW TABLES
My code is shown below, I can select * from employees but I cannot get any data back from SHOW FIELDS, I do not receive an error, in fact, something is returned but I'm not sure what. I can execute the command:

SHOW FIELDS FROM test.employees

in the Control Center just fine, even cmd.ExecuteReader will enter reader.Read() once, something is returned but what does it look like? ......

Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state.

I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different.

Example:
The 'objects' table looks like this:
object_id, name, room
1, Hammer, 1
2, Mirror, 1
3, Spade, 2

So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade.
Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table

player_id, object_id, room
12, 1, 2

So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*

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] {

Delimited Fields Or Unused Fields
I have a table which has, for instance, 5 fields and maybe people only need the first one to be filled in often.  So 4 might well be empty.  Is it better to join them into 1 delimited field or leave them as their own fields?
And - if I have some fields that are only relevant to some types of rows, is it better to have 1 field for them delimited, or have them as separate fields?  (the same issue there really)

The other side of it is - is it better to split such delimited fields in the mysql call, or split them afterwards in php?
I might know which is easier though I can do it either way now I've researched, but I don't know which is better.  I also don't know how big this db will get or how often these fields won't be used so it comes down more to the overall design issue of which is better.  I'm thinking efficiency, speed, db load, and so on.

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.