I have a small problem with an SQL statement with a GROUP_CONCAT.
I have this statement:
SELECT ni.*, GROUP_CONCAT(newsletter_item_receivers.value) AS receivers, nf.*, GROUP_CONCAT(nm.mailgroup_name) AS mailgroups
FROM newsletter_items ni
INNER JOIN newsletter_fields nf ON (nf.field_letter_uid = ni.letter_id)
LEFT JOIN newsletter_item_receivers ON (newsletter_item_receivers.letter_id = ni.letter_id)
INNER JOIN newsletter_mailgroups nm ON (FIND_IN_SET(nm.mailgroup_id, newsletter_item_receivers.value))
WHERE nf.field_name = 'letter_headline' AND ni.template = '". $template ."'
GROUP BY ni.letter_id
I have done something wrong, because my output is messed up.
I have 4 tables:
newsletter_items that have these columns:
letter_id (int)
letter_date (int)
template (varchar)
status (int)
and newsletter_item_receivers that have these columns:
rid (int)
letter_id (int)
value (int)
newsletter_mailgroups
mailgroup_id (int)
mailgroup_name (varchar)
newsletter_fields
field_uid (int)
field_name (varchar)
field_content (text)
field_letter_uid (int)
I get almost the output that I want, but my mailgroups are displayed wrong. I want it to be displayed like
Group A, Group C but it is displayed like
Group A, Group C, Group A, Group C, Group C when my newsletter_item_receivers tables contains this:
rid | letter_id | value
1 | 1 | 2
2 | 1 | 3
3 | 2 | 1
newsletter_item_receivers.letter_id refers to newsletter_items.letter_id and newsletter_item_receivers.value refers to newsletter_mailgroups.mailgroup_id
Is there someone who can see what I have done wrong?
