Sphinx query index: exclude rows containig particular value

Question

I have a Sphinx index with the following columns

id	article_id	country_id
1	1	1
2	1	2
3	1	3
4	2	2
5	2	3
6	3	4
7	4	1
8	4	2
9	5	1

I need to get article_ids which don't contain country_id = 1 at all therefore 2 and 3.

The problem is that Sphinx doesn't support joins, nor sub-queries so I just can create a simple select query and instead of 2 and 3 I get 1, 2, 3, 4 and only 5 is actually excluded.

Is there a way to build a query which will return 2 and 3 in Sphinx?

The simplified Sphinx index looks like that:

sql_query_pre = SET @a := 0;

    sql_query = \
\
SELECT \
@a := @a + 1 AS id, \
ase.id AS search_id, \
ase.name, \
ase.article_id, \
af.country_id, \
FROM articles_search ase \
LEFT JOIN articles a ON a.id = ase.article_id \
LEFT JOIN affiliation_article aa ON aa.article_id = ase.article_id \
LEFT JOIN affiliations af ON aa.affiliation_id = af.id \

sql_attr_bigint=country_id
sql_attr_bigint=article_id
}

and the query is

SELECT article_id, WEIGHT() w FROM articles WHERE MATCH('(@name nasicon)') AND country_id != 9 GROUP BY article_id ORDER BY w DESC LIMIT 0, 15 OPTION max_matches = 200

Answer 1

Seems like a clear case for HAVING

SELECT article_id, WEIGHT() w, SUM(country_id = 1) AS has1
FROM articles
WHERE MATCH('(@name nasicon)')
GROUP BY article_id
HAVING has1 = 0
ORDER BY w DESC
LIMIT 0, 15 OPTION max_matches = 200

The SUM counts country=1 in all rows in group (ie article), and only returns rows, where the article has no country=1 documents.