CODEWITHSUNDEEP
javamysqlsql
OHHH
OHHH

Reputation: 1051

Is there any way to count matches between 2 tables using sql?

Ok... I need to do this simple task... I can do it with java or python in a simple way... But is there any way to do this using sql and delagating the job to the database engine and not to jvm or python?

I have table 1 and table 2

for example

table 1 has 2 stored titles...

"love is a feeling", "harry potter"

table 2 has 3 stored titles...

"abc", "physics 1", "the trip"

Some are different, but some might be coincidential...

I need to compare each entry of table 2 with all the rows returned from a select title from table1...

so it would be like this

it compares "love is a feeling" with "abc" and "physics 1" and "the trip", if findes a match it increases the count...

it should do that with all the names and in the end I want to know how many coincidences I have...

as I say it can be done with java or any lang by querying: 

results = rows returned by using "select title from table1";
while results.next () {
 results2 = rows returned by using "select title from table2";

 while results2.next(){
    if current results == current results2
        count++;    
  }
}

print count

But thats not what I need...

Upvotes: 3

Views: 4740

Answers (3)

Mohammod Hossain
Mohammod Hossain

Reputation: 4114

SELECT COUNT(*) FROM table1 INNER JOIN table2 ON table1.title = table2.title

Upvotes: 1

John Woo
John Woo

Reputation: 263723

try joining both tables,

SELECT  a.title, COUNT(b.title) totalMatch
FROM    table1 a
            LEFT JOIN table2 b
                On a.title = b.title
GROUP BY a.Title

by using LEFT JOIN it will display 0 if it has no match.

SQLFiddle Demo

Upvotes: 4

eggyal
eggyal

Reputation: 125865

SELECT COUNT(*) FROM table1 JOIN table2 ON table1.title = table2.title

Learn about SQL joins.

Upvotes: 6

Related Questions