MySQL查询连接多个相关表

My question is about getting multiple values of a property into a string without duplicating the main row.

我的问题是关于将属性的多个值转换为字符串而不重复主行。

I will use an easy example to try to explain my problem:

我将使用一个简单的示例来尝试解释我的问题:

Entity - Relationship diagram

实体 - 关系图

With, for example, following data:

例如,有以下数据:

I want to get some elements of "TABLE" with a concatenation of Property_1 in a field and a concatenation of Property_2 in another field. But, it is important to never duplicate rows of "TABLE"

我希望得到一些“TABLE”元素,其中字段中的Property_1和另一个字段中Property_2的串联。但是,永远不要复制“TABLE”行是很重要的

For example, a query that get Row1 and Row4 should look like:

例如,获取Row1和Row4的查询应如下所示:

1 Row1 "P1_1, P1_2" "P2_1, P2_2"
4 Row4 "P1_3" "P2_3"

Example of database structure/data:

数据库结构/数据示例:

CREATE TABLE table1(
    id INT AUTO_INCREMENT,
    value VARCHAR(10),
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE property_1(
    id INT AUTO_INCREMENT,
    value VARCHAR(10),
    table1 INT,
    PRIMARY KEY(id),
    FOREIGN KEY (table1) REFERENCES table1(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE property_2(
    id INT AUTO_INCREMENT,
    value VARCHAR(10),
    table1 INT,
    PRIMARY KEY(id),
    FOREIGN KEY (table1) REFERENCES table1(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=latin1;
INSERT INTO table1 (value) VALUES 
    ('Row1'),('Row2'),('Row3'),('Row4'),('Row5'),('Row6');
INSERT INTO property_1 (value, table1) VALUES 
    ('P1_1', 1), ('P1_2', 1), ('P1_3', 4), ('P1_4', 6);
INSERT INTO property_2 (value, table1) VALUES 
    ('P2_1', 1), ('P2_2', 1), ('P2_3', 4), ('P2_4', 5);

I have tryed some queries like:

我试过一些问题,比如:

SELECT t.*, GROUP_CONCAT(p1.value), GROUP_CONCAT(p2.value) 
FROM table1 t 
    LEFT JOIN property_1 p1 ON p1.table1=t.id 
    LEFT JOIN property_2 p2 ON p2.table1=t.id 
WHERE t.id IN (1,4) 
GROUP BY t.id;

But the result is duplicating values in the property_1 and property_2 string.

但结果是复制了property_1和property_2字符串中的值。

So:

How to get distinct rows of table1 with their properties concatenated in strings?

如何获取不同的table1行,其属性在字符串中连接?

1 个解决方案

#1

Quick answer: use DISTINCT inside your group_concat - like so:

快速回答:在group_concat中使用DISTINCT - 就像这样:

SELECT t.*, GROUP_CONCAT(distinct p1.value), GROUP_CONCAT(distinct p2.value) 
FROM table1 t 
    LEFT JOIN property_1 p1 ON p1.table1=t.id 
    LEFT JOIN property_2 p2 ON p2.table1=t.id 
WHERE t.id IN (1,4) 
GROUP BY t.id;

SQLFiddle here.