Solved

Co0mparing two tables in mysql

Posted on 2012-03-24
9
294 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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

21 Experts available now in Live!

Get 1:1 Help Now