Solved

Co0mparing two tables in mysql

Posted on 2012-03-24
9
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

749 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