CODEWITHSUNDEEP
sql-servert-sql
Pedro
Pedro

Reputation: 75

Select rows that must contain certain values

I have two tables named Table A and Table B. Table A is a list of words and Table B is a list of documents related to those words. I need to do a search where I need to retrieve which documents have all the words I look for or some of them.

The tables structures and data example is as follow:

Declare @TableA Table (IdWord Int, Word Varchar(100))
Declare @TableB Table (IdProcess Int, IdWord Int, Document Varchar(100))

Insert Into @TableA Values (1, 'A');
Insert Into @TableA Values (2, 'B');
Insert Into @TableA Values (3, 'C');
Insert Into @TableA Values (4, 'D');
Insert Into @TableA Values (5, 'E');

Insert Into @TableB Values (1, 1, 'Document 1.doc');
Insert Into @TableB Values (1, 2, 'Document 1.doc');
Insert Into @TableB Values (1, 3, 'Document 1.doc');
Insert Into @TableB Values (1, 4, 'Document 1.doc');

Insert Into @TableB Values (2, 1, 'Document 2.doc');
Insert Into @TableB Values (2, 2, 'Document 2.doc');

Insert Into @TableB Values (3, 5, 'Document 3.doc');

I've been thinking how to resolve the following logic:

(Word='A' And Word='B' And Word='C') Or Word='E'

Which should yield as result Document 1.doc (because although it has more than 3 words it contains all the 3 words I'm looking for) And Document 3.doc (because or operator)

Any ideas?

Upvotes: 1

Views: 351

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Using HAVING:

SELECT Document
FROM @TableA A
JOIN @TableB B
  ON B.IdWord = A.IdWord
WHERE Word IN ('A', 'B','C', 'E')
GROUP BY Document
HAVING COUNT(DISTINCT CASE WHEN Word IN ('A','B', 'C') THEN Word END) = 3
    OR COUNT(DISTINCT CASE WHEN Word IN ('E') THEN Word END) = 1

db<>fiddle demo

Upvotes: 3

Related Questions