mysql – matching records

Problem:
Find records that match within two tables, TableA and TableB.

Solution:

Use a simple SELECT statement with a defined WHERE clause

Lets assume that TableA and TableB have a field called “Phone” which contains the phone number of the clients and these numbers are obviously stored in a unique index. So what we are going to do is run a simple query to find out which numbers in TableB match TableA.

Assuming you are using linux and you can run this query from the command prompt, this is the way to do it:

user@server:~/ mysql -u USERNAME -p
(the server will ask for your password, enter the password and press Enter)

Now you should be in mysql and the prompt should look different already

mysql> use DATABASENAME
(this command changes the database to the one where you have both the tables)
mysql> select * FROM TableB, TableA WHERE TableB.phone=TableA.phone;

This query will obviously select all fields from TableB and TableA. If you only wanted to select certain fields (for example “name” from TableB and “name” from TableA), then the query would change to:

mysql> select TableB.name, TableA.name FROM TableB, TableA WHERE TableB.phone=TableA.phone;

This will give you just the name column from TableB and name column from TableB.

Now what if you wanted to dump the query result into a text file for future use? Here’s the query:

mysql> SELECT TableB.name, TableA.name FROM TableB, TableA WHERE TableB.phone=TableA.phone INTO OUTFILE ‘/tmp/matchingnames.txt';

That should do it.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">