CODEWITHSUNDEEP
mysqlaggregatestring
cappymixed
cappymixed

Reputation: 15

How can I write a query to aggregate multiple strings into one field?

I am trying to merge three tables. The first table has id and data_name, second table data_id, option_id and property, third table user_id, data_id.

For example:

first table
+----+-----------+
| id | data_name |
+----+-----------+
| 1  + veri1     |
| 2  + veri2     |
| 3  + veri3     |
+---++-----------+

second table 
+----------+----------+-----------+
data_id    | property | option_id |
+----------+----------+-----------+
| 1        | blue     | 1         |
| 1        | cold     | 2         |
| 2        | gray     | 1         |
| 2        | hot      | 2         |
| 3        | green    | 1         |
| 3        | cold     | 1         |
+----------+----------+-----------+

third table
+----------+-----------+
| user_id  | data_id   |
+----------+-----------+
| 1        | 2         |
| 2        | 3         |
| 3        | 1         |
+----------+-----------+

I want to get:

user: 1
data: veri2
properties: gray - hot

How do I write the SQL for this?

Upvotes: 0

Views: 44

Answers (2)

Masoud
Masoud

Reputation: 157

This query should do it. But your property will be comma separated instead of -.

Select user_id,data_name, GROUP_CONCAT(property)
From Table1 INNER JOIN Table2 ON Table1.id = Table2.data_id
INNER JOIN Table3 ON Table1.id = Table3.data_id
GROUP BY Table1.id

Upvotes: 1

leancz
leancz

Reputation: 688

First start with the FROM clause. Where is the data stored that you want?

FROM user_first_table
JOIN property_second_table
JOIN user_data_third_table

Then consider the conditions you should join with (I find it easier to re-order the tables)

FROM user_data_third_table t 
JOIN user_first_table f ON f.id = t.user_id
JOIN property_second_table s ON s.data_id = t.data_id

Then narrow down with a WHERE clause, e.g. only want data for veri1

FROM user_data_third_table t 
JOIN user_first_table f ON f.id = t.user_id
JOIN property_second_table s ON s.data_id = t.data_id
WHERE f.data_name = 'veri1'

Then label the fields of data you want

SELECT f.id as 'user', f.data_name as 'data', s.property as 'properties'
FROM user_data_third_table t 
JOIN user_first_table f ON f.id = t.user_id
JOIN property_second_table s ON s.data_id = t.data_id
WHERE f.data_name = 'veri1'

Upvotes: 1

Related Questions