Solved

Co0mparing two tables in mysql

Posted on 2012-03-24
9
296 Views
Last Modified: 2012-03-27
I would like to compare two tables.  I need to compare multiple columns and count them.  I will need to compare upto 6 columns.  I found this query but have been unable to modify it for multiple columns.

SELECT * FROM tblA
 WHERE tblA.ID
 NOT IN (
 SELECT tblB.ID
 FROM tblB
 WHERE tblA.ID=tblB.ID
 )
0
Comment
Question by:ralphs1961
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Assisted Solution

by:nishant joshi
nishant joshi earned 100 total points
ID: 37762114
means you want to need that record from A table which not in B table.
Then it is advisable to use outer join.

SELECT * FROM tblA a
Outer Join tblB b
ON a.ID <> b.ID --AND more colums

Open in new window


Regards,
nishant
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37762204
Agree with Nishant that an outer join is what is needed, except I usually do it this way:

select a.* from table1 a left outer join table2b
on (a.col1=b.col1 and a.col2=b.col2 and .... a.col6=b.col6)
where b.pimary_key_column is null;
0
 

Author Comment

by:ralphs1961
ID: 37767826
Thanks for the responses and I am going to try both.

It seems I made a mistake in my original question, what I am after is a count of rows that are the same.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 24

Accepted Solution

by:
johanntagle earned 400 total points
ID: 37768663
If what you need are the same, the query is different:

select count(a.primary_key_column) from table1 a join table2b
on (a.col1=b.col1 and a.col2=b.col2 and .... a.col6=b.col6);

a.primary_key_column can actually be any column, but I usually recommend the primary key, especially if it's innodb.
0
 

Author Comment

by:ralphs1961
ID: 37768791
Thanks very much you both, I ran the query and it worked, It took 1/2 hour to complete, is there anyway to make it faster?  3,000 rows being compared to 14 million rows...  

I do have indexes setup but if you have any ideas please let me know.  If you think this last question should be a new question please let me know.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37768811
See if a concatenated index with all 6 columns will help.  Make sure you have them on both tables.  If it still doesn't help, please post output of EXPLAIN <query>
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37769617
;with t1(cnt,id) as 
(
SELECT 1,a.ID FROM tblA a
Outer Join tblB b
ON a.ID <> b.ID
group by a.ID
)
;with t2 as
(
SELECT cnt+1,id from t1
)
select cnt from t2

Open in new window

use recurcive cte..
Regards,
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37769664
Nishant, does mysql already support that?  Last time I checked it still lagged behind the SQL standard and won't accept that yet.
0
 

Author Comment

by:ralphs1961
ID: 37771925
I would like to thank both of you for your help.  I am going to award johanntagle 400 points as he made the suggestion of concatenating the columns into a index.  Turned a half hour query into 5 seconds.  Thanks.  Both of the original queries worked and worked well with out the memory out error I was getting with the first query.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
showing numeric numbers 2 38
sort in mysql based off of query param 4 26
Creating Functions in phpMyAdmin 8 26
Complex MySQL Query 2 33
Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question