用逗号值联接表 [英] Join tables with comma values

问题描述

我很难接受3张桌子. 我有一个newsletter_items，newsletter_fields和newsletter_mailgroups，我想加入其中以获得新闻通讯的列表.

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.

newsletter_items包含以下字段:

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包含以下字段:

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

其中field_letter_uid是该字段所属的新闻通讯.

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

和newsletter_mailgroups包含以下字段:

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

我想要的是将这3个表组合在一起，这样我就可以获得所有新闻通讯的列表:

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

因此，简而言之，我希望我的SQL查询能够联接这3个表，并在此过程中从mailgroup表中选择接收者，并以逗号分隔，如Group1，Group 2一样显示它们.

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

这就是我现在得到的

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.

推荐答案

我建议您使连接明确.
它使调试查询和使用左联接更改内部更容易.
绝对没有理由使用SQL '89隐式联接语法.

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

然后从表newsletter_items

您的查询然后更改为:

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.

这篇关于用逗号值联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！