Hello, i'm stuck on a problem with my query.
First i had this query to get posts based on selected categories.
SELECT SQL_CALC_FOUND_ROWS posts.ID, posts.post_title FROM posts INNER JOIN term_relationships ON (posts.ID = term_relationships.object_id) WHERE 1=1 AND ( SELECT COUNT(1) FROM term_relationships WHERE term_taxonomy_id IN (7) AND object_id = posts.ID ) = 1 AND ( SELECT COUNT(1) FROM term_relationships WHERE term_taxonomy_id IN (16,17) AND object_id = posts.ID ) = 1 AND posts.post_type = "post" AND posts.post_status = "publish" GROUP BY posts.ID ORDER BY posts.post_date DESC
The 2 end clauses there are dynamicly added, so with this query it selects posts that are in ( category 7 ) AND in ( category 16 or 17 )
This works great... But then i found out the query doesn't work when custom taxonomies are used, so now i'm stuck with the query below;
SELECT ID, post_title FROM posts LEFT JOIN term_relationships ON(posts.ID = term_relationships.object_id) LEFT JOIN term_taxonomy ON(term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id) LEFT JOIN terms ON(term_taxonomy.term_id = terms.term_id) WHERE 1=1 AND terms.term_id IN (7) AND terms.term_id IN (16,17) AND term_taxonomy.taxonomy = 'category' AND posts.post_status = 'publish' AND posts.post_type = 'post'
When i use the AND .... IN() more than once it stops returning anything. When i do it once, it works.
I hope someone can help me out :)