Multiple Views filters criteria : query with LEFTJOIN instead of INNERJOIN? I created a view with 2 FILTER CRITERIA (Content: Has taxonomy term) exposed to visitors.

For testing purpose, I have 2 vocabularies with 2 terms inside :

Vocabulary 1


T1 
T2


Vocabulary 2


T3
T4


I have 2 articles. Article 1 is only PART of T1.

If I select T1 and Any, I have no results but I should have result because I use OR operator(see screen shot).

Is the view filter criteria correct ?


If this article is T1 and T3 and i select T1 and T3, it works. If I select any and any, it works and i see my 2 articles.

See 2 cases :

1)


2)


How can I have a query with LEFTJOIN instead of INNERJOIN ?
Checking the "reduce duplicates" fixed the issue for me.



In "Views setting" (admin/structure/views/settings), check "Show the SQL query"

Then, we can see the SQL query built with views.

With reduce duplicates, INNER JOIN are converted to LEFT JOIN, which is exactly what I need I think.

When it was unchecked, the SQL query was :

SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM 
{node_field_data} node_field_data
INNER JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
LEFT JOIN {taxonomy_index} taxonomy_index2 ON node_field_data.nid = taxonomy_index2.nid AND taxonomy_index2.tid != '2'
WHERE ((taxonomy_index.tid = '2')) AND ((taxonomy_index2.tid = '10'))
ORDER BY node_field_data_created DESC
LIMIT 11 OFFSET 0


When it is checked :

SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM 
{node_field_data} node_field_data
LEFT JOIN {taxonomy_index} taxonomy_index_value_0 ON node_field_data.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_0.tid = '2'
LEFT JOIN {taxonomy_index} taxonomy_index_value_1 ON node_field_data.nid = taxonomy_index_value_1.nid AND taxonomy_index_value_1.tid = '10'
WHERE (taxonomy_index_value_0.tid = '2') OR (taxonomy_index_value_1.tid = '10')
ORDER BY node_field_data_created DESC
LIMIT 11 OFFSET 0