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.

Inflexed

And now you know one of the reasons why you shouldn't put comma separated (or otherwise serialized) values in a relational DB. – Pau

... unless this DB is PostgreSQL with native support for arrays and pretty unnest function that expands an array to a set of rows – Tabbatha

@J0HN: Pretty or not, they will be slow compared to a normalized table and a JOIN. Not to mention the other problems, like enforcing integrity. – Anachronous

@ypercube You have some performance benchmarks, or just saying so? As for integrity - there are foreign keys on int arrays in Postgre. But anyway, they are designed not to be used as foreign/primary keys, and using arrays in this case is discouraged. – Tabbatha

@J0HN: No, I have no benchmarks for Postgres array vs. JOIN. For MySQL, yes, the functions that can achieve similar functionality (but not so advanced as arrays) FIND_IN_SET() and GROUP_CONCAT() are slower than Joining normalized tables. – Anachronous

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.

Gunar

Thanks a lot all of you! I really appreciate it! When I use this one above I get this error: Unknown column 'ni.receivers' in 'on clause' – Inflexed

The query is correct. I think you copied the following line incorrectly: FROM newsletter_items ni <<-- you dropped the ni here, Either that or you don't have a field called receivers – Gunar

I forgot the field receivers! Sorry about that! I have accepted your answer! Thank you very much! – Inflexed

Answer 2

If it's allowed, why don't you create a new table called newsletter_item_receivers where you could store letter_id, receiver_id fields? Having comma separated values in a field like this usually means you're missing a table :)

Edit:

By using CSV, you are making your life miserable when you want to retrieve an answer to "give me all newsletters that receiver_id=5 receives" :)

Here's a good answer to a similar question on SO: Comma separated values in a database field

Edit2:

If I understand your table relationships correctly then it would be something like this:

SELECT
  a.letter_date,
  b.receiver_id,
  a.status
FROM newsletter_items_receivers b
  LEFT OUTER JOIN newsletter_items a ON (a.letter_id = b.letter_id)
  LEFT OUTER JOIN newsletter_mailgroups m ON (m.mailgroup_id = b.receiver_id)

NOTE! This query WILL NOT return a newsletter when there are no receivers of that newsletter. If you need that functionality you can try something like this:

SELECT
  x.letter_date,
  y.mailgroup_name,
  x.status
FROM (
SELECT
  a.letter_date,
  b.receiver_id,
  a.status
FROM newsletter_items a
  LEFT OUTER JOIN newsletter_items_rec b ON (b.letter_id = a.letter_id)) x
  LEFT OUTER JOIN newsletter_mailgroups y ON (y.mailgroup_id = x.receiver_id)

I don't have access to SQL right now so I might have made some syntax errors (hopefully not logical ones :)).

As for why we are doing it like this, as @Konerak pointed out, you'd be well advised to read up on database normalization and why it's important.

You can start with this article from about.com, just glanced over it seems an OK read http://databases.about.com/od/specificproducts/a/normalization.htm

Also, it would be good if you'd keep fields names the same across multiple tables. For example you have letter_id in newsletter_items, but you have field_letter_uid in newsletter_fields. Just a thought :)

Vermicide

You should probably include something about normalization in your answer ;) – Gloucester

I didn't even think about not using CSV in my DB... I could try to use a new table – Inflexed

Could you give me an example to join the tables when I have newsletter_item_receivers table with letter_id and receiver_id? – Inflexed

Answer 3

Try to use

SELECT A.*, B.*, group_concat(C.mailgroup_name SEPARATOR ',') 
FROM newsletter_items A,  newsletter_fields B, newsletter_mailgroups C  
WHERE B.field_letter_uid = A.letter_id 
      AND field_name = 'letter_headline' 
      AND A.template = '". $template ."' 
      and find_in_set(c.mailgroup_id, A.receivers) 
group by A.letter_id;

Rusticate