StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL query concatenating more than 1 related table
text
Body
copied!<p>My question is about getting multiple values of a property into a string without duplicating the main row.</p> <p>I will use an easy example to try to explain my problem:</p> <p>Entity - Relationship diagram</p> <p><img src="https://i.stack.imgur.com/UJ3Br.jpg" alt="Diagram"></p> <p>With, for example, following data:</p> <p><img src="https://i.stack.imgur.com/VNYTg.png" alt="enter image description here"></p> <p>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"</p> <p>For example, a query that get Row1 and Row4 should look like:</p> <pre><code>1 Row1 "P1_1, P1_2" "P2_1, P2_2" 4 Row4 "P1_3" "P2_3" </code></pre> <p>Example of database structure/data:</p> <pre><code>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); </code></pre> <p>I have tryed some queries like:</p> <pre><code>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; </code></pre> <p>But the result is duplicating values in the property_1 and property_2 string.</p> <p>So:</p> <p><strong>How to get distinct rows of table1 with their properties concatenated in strings?</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload