CODEWITHSUNDEEP
mysqlsql
corvid
corvid

Reputation: 11187

return all ids where the count in table1 is equal to the count in table2

I am joining two tables by the field of ids. Let's call these tables p and k. k is a superset of p in this situation; all things in p are in k. The same id may show up multiple times or have multiple entries.

The idea, however, is I want to see if the count of times the id shows up in p is exactly the same as the number of times it shows up in k. If it is, those are the ids I want to show in my query.

This is all I really have to start.

SELECT
  p.id
FROM
  all_files k,
  specific_files p
WHERE
  p.id = k.id

How would this be done?

Upvotes: 0

Views: 30

Answers (1)

Lamak
Lamak

Reputation: 70638

First of all, you should always use proper explicit joins instead of implicit ones. Now, for your query, you could do:

SELECT *
FROM (  SELECT id, COUNT(*) AS N
        FROM specific_files
        GROUP BY id) AS p
INNER JOIN (SELECT id, COUNT(*) AS N
            FROM all_files
            GROUP BY id) AS k
    ON p.id = k.id
    AND p.N = k.N

Upvotes: 2

Related Questions