Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL selecting rows where one column's value is common across another criteria column

Tags:

sql

relational

I have a cross reference table that looks like this:

id  document_id  subject_id
1   8            21
2   5            17
3   5            76
4   7            88
5   9            17
6   9            76
7   2            76

It matches documents to subjects. Documents can be members of more than one subject. I want to return rows from this table where a given document matches all the subjects in a given set. For example, given the set of subjects:

(17,76)

I want to return only rows for documents which match all the subjects in that set (at least) somewhere in the cross reference table. The desired output set given the above set would be:

id  document_id  subject_id
2   5            17
3   5            76
5   9            17
6   9            76

Notice that the last row of the table is not returned because that document only matches one of the required subjects.

What is the simplest and most efficient way to query for this in SQL?

like image 724
Maciek Avatar asked Sep 10 '09 22:09

Maciek


People also ask

How do you fetch data that is common in two query results?

Use the UNION ALL clause to join data from columns in two or more tables. In our example, we join data from the employee and customer tables. On the left of the UNION ALL keyword, put the first SELECT statement to get data from the first table (in our example, the table employee ).

How do you select common values in SQL?

The SQL intersect operator allows us to get common values between two tables or views. The following graphic shows what the intersect does. The set theory clearly explains what an intersect does. In mathematics, the intersection of A and B (A ∩ B) is the set that contains all elements of A that also belong to B.

Which clause is used to retrieve values with similar matches?

The SQL LIKE clause is used to compare a value to similar values using wildcard operators.


1 Answers

I assume that the natrual key of this table is document_id + subject_id, and that id is a surrogate; IOW, document_id and subject_id are unique. As such, I'm just going to pretend it doesn't exist and that a unique constraint is on the natural key.

Let's start with the obvious.

SELECT document_id, subject_id
  FROM document_subjects
 WHERE subject_id IN (17,76)

That gets you everything you want plus stuff you don't want. So all we need to do is filter out the other stuff. The "other stuff" is groups of rows having a count that is not equal to the count of the desired subjects.

SELECT document_id
  FROM document_subjects
 WHERE subject_id IN (17,76)
 GROUP BY document_id
HAVING COUNT(*) = 2

Note that subject_id is removed because it doesn't participate in grouping. Taking this one step further, i'm going to add an imaginary table called subjects_i_want that contains N rows of subjects you want.

SELECT document_id
  FROM document_subjects
 WHERE subject_id IN (SELECT subject_id FROM subjects_i_want)
 GROUP BY document_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM subjects_i_want)

Obviously subjects_i_want could be swapped out for another subquery, temporary table, or whatever. But, once you have this list of document_id, you can use it within a subselect of a bigger query.

SELECT document_id, subject_id, ...
  FROM document_subjects
 WHERE document_id IN(
        SELECT document_id
          FROM document_subjects
          WHERE subject_id IN (SELECT subject_id FROM subjects_i_want)
          GROUP BY document_id
         HAVING COUNT(*) = (SELECT COUNT(*) FROM subjects_i_want))

Or whatever.

like image 200
Alex Papadimoulis Avatar answered Oct 24 '22 04:10

Alex Papadimoulis