用逗号值连接表 [英] 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 个表，并在该过程中从邮件组表中选择接收者并以逗号分隔显示它们，如 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

You then remove the field receiver from the table 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屋！