Richard S. Richard S. - 20 days ago 6
SQL Question

SQL index based search

I have a table called

Index
which has the columns
id
and
value
, where
id
is an auto-increment bigint and
value
is a varchar with an english word.

I have a table called
Search
which has relationships to the table
Index
. For each search you can define which indexes it should search in a table called
Article
.

The table
Article
also has relationships to the table
Index
.

The tables which define the relationships are:

Searches_Indexes
with columns
id_search
and
id_index
.

Articles_Indexes
with columns
id_article
and
id_index
.

I would like to find all Articles that contain the same indexes of Search.

For example: I have a
Search
with indexes
laptop
and
dell
, I would like to retrieve all
Article
s which contain both indexes, not just one.

So far I have this:

SELECT ai.id_article
FROM articles_indexes AS ai

INNER JOIN searches_indexes AS si
ON si.id_index = ai.id_index

WHERE si.id_search = 1


How do I make my SQL only return the
Article
s with all the
Index
es of a
Search
?

Edit:

Sample Data:

Article:

id | name | description | ...
1 | 'Dell Laptop' | 'New Dell Laptop...' | ...
2 | 'HP Laptop' | 'Unused HP Laptop...' | ...
...


Search:

id | name | id_user | ...
1 | 'Dell Laptop Search' | 5 | ...


Index:

id | value
1 | 'dell'
2 | 'laptop'
3 | 'hp'
4 | 'new'
5 | 'unused'
...


Articles_Indexes:

Article
with
id
1 (the dell laptop) has the
Index
es 'dell', 'laptop', 'new'.

Article
with
id
2 (the hp laptop) has the
Index
es 'laptop', 'hp', 'unused'.

id_article | id_index
1 | 1
1 | 2
1 | 4
...
2 | 2
2 | 3
2 | 5
...


Searches_Indexes:

Search
with
id
1 only contains 2
Index
es, 'dell' and 'laptop':

id_search | id_index
1 | 1
1 | 2


Required output:

id_article
1

Answer

If I understand correctly, you want aggregation and a HAVING clause. Assuming there are no duplicate entries in the indexes tables:

SELECT ai.id_article 
FROM articles_indexes ai INNER JOIN
     searches_indexes si 
     ON si.id_index = ai.id_index 
WHERE si.id_search = 1
GROUP BY ai.id_article
HAVING COUNT(*) = (SELECT COUNT(*) FROM searches_indexes si2 WHERE si2.id_search = 1);

This counts the number of matches and makes sure it matches the number you are looking for.

I should add this. If you wanted to look for all searches at the same time, I'd be inclined to write this as:

SELECT si.id_search, ai.id_article 
FROM articles_indexes ai INNER JOIN
     (SELECT si.*, COUNT(*) OVER (PARTITION BY si.id_index) as cnt
      FROM searches_indexes si 
     ) si
     ON si.id_index = ai.id_index 
GROUP BY si.id_search, ai.id_article, si.cnt
HAVING COUNT(*) = si.cnt;