How to add categories to a SQL query that outputs channel entry data

Question

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.

SELECT
  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;

Answer 1

Not sure if it is best way to do this, but something like this:

SELECT
  ct.title AS entry_title,
  cd.field_id_6 AS custom_field_6,
  (SELECT ca.cat_name FROM exp_category_posts cp INNER JOIN exp_categories ca ON cp.cat_id = ca.cat_id WHERE entry_id = cd.entry_id AND ca.group_id = 1 LIMIT 1) AS cat_1,
  (SELECT ca.cat_name FROM exp_category_posts cp INNER JOIN exp_categories ca ON cp.cat_id = ca.cat_id WHERE entry_id = cd.entry_id AND ca.group_id = 1 LIMIT 1) AS cat_2
FROM exp_channel_data cd
INNER JOIN exp_channel_titles ct ON cd.entry_id = ct.entry_id
WHERE cd.channel_id = 2;