I’m trying to pull in a list of events from a channel that spans two category groups. One is an industry sector, whilst the other is event type.
I need to write a query that will pull in all entries from a specific sector category (in this instance category id 6) AND at least one event category (175 or 177).
So far I have this:
<ul>
{exp:query sql="SELECT
type.cat_id,
type.cat_name,
type.cat_url_title,
t.title as title,
t.status,
d.channel_id,
d.site_id,
d.field_id_32 as date
FROM
exp_categories type JOIN
exp_category_posts p ON type.cat_id = p.cat_id JOIN
exp_channel_titles t ON p.entry_id = t.entry_id JOIN
exp_channel_data d ON t.entry_id = d.entry_id
WHERE
t.status = 'open' AND
d.site_id = 1 AND
d.channel_id = 3 AND
type.group_id = 2 AND
(p.cat_id = 7 AND ((p.cat_id = 175) OR (p.cat_id = 177))) AND
t.channel_id = 2
GROUP BY
type.cat_id
ORDER BY
type.cat_order
LIMIT 5"}
{if no_results}<li>Sorry, we don't have any events listed</li>{/if}
<li>{date} {title}</li>
{/exp:query}
</ul>
But it’s not returning any results. If I do the same search in the cp (with Zenbu’s combined category search) I get seven results for one combination and three for the other, so either way there are entries to be listed. Unfortunately the way zenbu’s queries are built I can’t use the one that generates that list (as output in the ouput profiler)
Can anyone suggest a query that will achieve what I need?