php - Join tables with comma values

Question

I have a hard nut to crack with joing 3 tables. I have a newsletter_items, newsletter_fields and newsletter_mailgroups which I want to be joined to get a list of newsletters.

The newsletter_items contains the fields:

letter_id, letter_date, receivers, template, status

That can look like

    1, 1234567899, 1,2 (comma separated), standard.html, 1

newsletter_fields contains the fields:

    field_uid, field_name, field_content, field_letter_uid

That can look like 

    1, letter_headline, A great headline, 1

where field_letter_uid is the newsletter for which the field belongs to.

and newsletter_mailgroups contains the fields:

mailgroup_id, mailgroup_name, number_of_members

That can look like 

        1, Group1, 233
        2, Group2, 124
        3, Group3, 54

What I want is to combine these 3 tables to that I can get a list of all the newsletter like this:

Letter date | Letter headline | Receivers | Status

2008-01-01 12:00:00 | A great headline | Group1, Group 2 | 1

So in short I want my SQL query to join the 3 tables and in that process select the receivers from the mailgroup table and display them comma separated like Group1, Group 2

This what I got now

SELECT A.*, B.* FROM newsletter_items A, newsletter_fields B, WHERE B.field_letter_uid = A.letter_id AND field_name = 'letter_headline' AND A.template = '". $template ."'; 

But I can't seem to figure out how to get the mailgroups into that.

Answer 1

I recommend that you make your joins explicit.
It makes it easier to debug your query and to change inner with left joins.
There is absolutely never a good reason to use SQL '89 implicit join syntax.

SELECT ni.*
       , 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)
INNER JOIN newsletter_mailgroups nm  
  ON (find_in_set(nm.mailgroup_id, ni.receivers))
WHERE  
  nf.field_name = 'letter_headline' 
  ni.template = '". $template ."' 
GROUP BY ni.letter_id;

Regarding your database design.
I recommend you normalize your database, that means that you move the comma separated fields into a different table.

So you make a table receivers

Receivers
----------
id integer auto_increment primary key
letter_id integer not null foreign key references newsletter_items(letter_id)
value integer not null

You then remove the field receiver from the table newsletter_items

Your query then changes into:

SELECT ni.*
       , group_concat(r.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)
INNER JOIN newsletter_mailgroups nm  
  ON (find_in_set(nm.mailgroup_id, ni.receivers))
LEFT JOIN receiver r ON (r.letter_id = ni.letter_id)
WHERE  
  nf.field_name = 'letter_headline' 
  ni.template = '". $template ."' 
GROUP BY ni.letter_id;

This change should also speed up your query significantly.