I'm trying to create a query that will output all the entries in a channel. I'm wanting to output the title, some custom fields, and the categories that the entry belongs to. Each entry will belong to one category in Category Group 1, and one category in Category Group 2 (as I'm using the excellent Category Field add-on to ensure this).
I'm not great with SQL, so I've only worked out how to output the title and custom fields so far. I'm getting stuck on how to output the category names from each category group that each entry belongs to, so was hoping that someone could help me out with this.
Here's my SQL so far (stripped back as far as I can), and the two commented-out category fields are where I'd like the category names to appear (except I can't figure out how to add the extra joins to pull this information in - linking the entry ID with the exp_category_posts table, and pulling in the category name from the exp_categories table). Thanks in advance for any help with this.
title AS 'Entry Title',
field_id_1 AS 'Custom Field 1',
# exp_categories.cat_name AS 'Category Group 1 Category Name',
# exp_categories.cat_name AS 'Category Group 2 Category Name',
FROM exp_channel_data cd
INNER JOIN exp_channel_titles ct ON cd.entry_id = ct.entry_id
WHERE ct.channel_id = 4;