Solved

Co0mparing two tables in mysql

Posted on 2012-03-24
9
293 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now