Better method for matching sets of identifiers in a MySQL statement
I am currently running some SQL that uses an IN clause to match multiple IDs. However, I would ideally like to be able to map certain IDs to others so that they appear together to return a result. Here's an example:
Edit: The IDs I'm matching are part of a many-to-many relationship. The structure looks like this:
Articles ArticleKeywords Keywords
An article can have multiple keywords associated with it by the ArticleKeywords table. I am currently using the IN clause to match any keyword identifier with respect to records. However, I would like to match certain records to small groups of keywords, that is, the keyword must appear with a different keyword for the record to be matched.
Current: ... And id IN ('25', '566', '156', '166', '7345')
More specifically: ... AND ((id = '25' AND id = '566') OR (id = '156' AND id = '166') OR (id = '7345'))
While the second option might work, I think it probably won't be very productive. Is there another way this can be done, or should I go about it differently?
Thanks for your help.
a source to share
Edited based on comments. Let's say you're looking for articles that:
- Enter a keyword named 'a1'
- Or, both keyboard characters are named "b1" and "b2"
- Or, you have either the keyword 'c1', 'c2' or 'c3'
You can query like:
select a.id
from Articles a
inner join ArticleKeywords ak on ak.articleid = a.id
inner join Keywords k on k.id = ak.keywordid
group by a.id
having
sum(case when k.name in ('a1') then 1 else 0 end) = 1
or sum(case when k.name in ('b1','b2') then 1 else 0 end) = 2
or sum(case when k.name in ('c1','c2,'c3') then 1 else 0 end) > 0
In SquareCog's comment, you can significantly improve performance with the WHERE clause. The sentence restricts the grouping to only relevant keywords. In the above query, add the WHERE just before the HAVING:
...
inner join Keywords k on k.id = ak.keywordid
where k.name in ('a1','b1','b2','c1','c2','c3')
group by a.id
...
You can get other details of the article (s), for example:
select *
from Articles
where id in (
...query from above here...
)
Say you have a table containing search groups defined as:
groupid - keywordid
1 - 1
1 - 2
2 - 3
The article value must match ((keyword 1 and keyword2) or keyword3). Then you can request the following:
select ak.articleid
from ArticleKeywords ak
inner join Search s on ak.keywordid = s.keywordid
group by s.searchgroup, ak.articleid
having count(*) = (
select count(*) from #Search s2 where s2.Searchgroup = s.SearchGroup
)
a source to share
Well your second option will never work ...
((id = '25' AND id = '566') --For this to return the column `id` would have to = both 25 & 566 which it obviously can't
OR
(id = '156' AND id = '166') --For this to return the column `id` would have to = both 156 & 166 which it obviously can't
OR
(id = '7345'))
What exactly are you trying to achieve ... What do you mean by "match certain identifiers with others so that they appear together." Do you mean consecutive lines?
a source to share
the answer provided by Andomar using GROUP BY
and HAVING
is a common way of solving this kind of problem, but it doesn't work very well. GROUP BY
often calls a temporary table.
Let's go back to your example:
... AND ((id = '25' AND id = '566') OR (id = '156' AND id = '166') OR (id = '7345'))
This can never be true. The terms in the sentence WHERE
are applied one line at a time. A column can id
never have two values in a given row. I understand conceptually what you are going to test, but this is not how SQL works.
When you need to write a condition that includes values occurring on multiple lines, another solution is to use self-join .
SELECT *
FROM ArticleKeywords k1
LEFT OUTER JOIN ArticleKeywords k2 ON (k1.article_id = k2.article_id)
WHERE k1.keyword_id = '7345'
OR (k1.keyword_id = '25' AND k2.keyword_id = '566')
OR (k1.keyword_id = '156' AND k2.keyword_id = '166');
Most people will call table aliases k1
and k2
". But if you think of these aliases by pointing to individual rows in a table, it instead becomes clearer how to write conditions using self-joins.
Of course, if you need to test a small group of three values instead of two values, you need to do another self-join.
This solution may not be able to use indexes efficiently, but it will not result in a temporary table that the solution executes GROUP BY
. Try both solutions, use EXPLAIN
them to analyze them and then measure their performance for comparison.
a source to share