Count common rows between two tables in mysql

Question

so here's my question...

Hi have two tables in mysql, called go_H and go_J, both looking like this:

go_H

+---------------+------------+ | gene | GoCode | +---------------+------------+ | DNAJC25-GNG10 | GO:0004871 | | DNAJC25-GNG10 | GO:0005834 | | DNAJC25-GNG10 | GO:0007186 | | LOC100509620 | GO:0005215 | | LOC100509620 | GO:0006810 | | LOC100509620 | GO:0016021 | | PPIAL4E | GO:0000413 | | PPIAL4E | GO:0003755 | | PPIAL4E | GO:0005737 | | PPIAL4E | GO:0006457 | | LOC105371242 | GO:0000413 | +----------------------------+

go_J

+------------+ | GoCode | +------------+ | GO:0007254 | | GO:0007256 | | GO:0007257 | | GO:0042655 | | GO:0043506 | | GO:0043507 | | GO:0043508 | | GO:0046328 | | GO:0046329 | | GO:0046330 | +------------+

Basically what I want to achieve is to see what GoCode values from go_J appear in GoCode from Go_H, and count them, so as I get a total number o GO ids that are present in both tables.

I have come to select go_H.GoCode and go_J.GoCode, but I don't know how to compare them to find common rows and then count them...

Any help?

Learn what is JOIN in mysql

u_mulder
– u_mulder

2016-12-07 16:59:23 +00:00
Commented Dec 7, 2016 at 16:59 — u_mulder
– u_mulder, Commented Dec 7, 2016 at 16:59

Community · Accepted Answer · 2017-05-23 11:45:43Z

SELECT COUNT(*) FROM go_H INNER JOIN go_J USING GoCode

INNER JOIN => Rows that are in both tables based on the join column (GoCode)

Alternative:

SELECT COUNT(*) FROM go_H h INNER JOIN go_J ON j.GoCode = h.GoCode

Check this answer out to learn about joins:

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Faizan Younus · Accepted Answer · 2016-12-07 17:00:18Z

1

Hope this helps.

select count(*) from go_J j join go_H h on h.GoCode=j.GoCode;

answered Dec 7, 2016 at 17:00

Faizan Younus

8031 gold badge9 silver badges14 bronze badges

Comments

Sercan REYHANLI · Accepted Answer · 2016-12-07 17:04:36Z

To find how many rows are similar between 2 table

SELECT COUNT(*) totalCount FROM go_H a INNER JOIN go_J b ON a.GoCode = b.GoCode

To find how many rows from go_H are not in go_J

SELECT COUNT(*) totalCount FROM go_H a LEFT JOIN go_J b ON a.GoCode = b.GoCode WHERE b.GoCode IS NULL

To find how many rows from go_J are not in go_H

SELECT COUNT(*) totalCount FROM go_J a LEFT JOIN go_H b ON a.GoCode = b.GoCode WHERE b.GoCode IS NULL

dinnertoast · Accepted Answer · 2016-12-07 17:08:35Z

You can achieve this just in SQL by running a query similar to this:

SELECT *, count (GoCode) FROM ( SELECT GoCode FROM go_H UNION SELECT GoCode FROM go_H )a group by a.gocode

This will provide you a table with each code in a column and then the amount of times it is present across both tables

An alternative with PHP would be get both tables into an array by using PDO and use in_array to check

<?php foreach ($go_H as $GoCode) { if (in_array($GoCode, $go_J)) { // handle codes in both tables } }

This is not the most efficient method but it will yeild results.

Collectives™ on Stack Overflow

Count common rows between two tables in mysql

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related