SQL查询 - 在一个结果行中显示连接结果[重复]

Question

This question already has an answer here:

• Can I concatenate multiple MySQL rows into one field? 11 answers
• MySQL DISTINCT on a GROUP_CONCAT() 6 answers

I want to create an advanced search based on one table and other tables my tables:

estate :
    +------+-------------+-------------------+-------------------+
    | id   |     title   |       cat_id      |  date              |
    +------+-------------+-------------------+-------------------+
    |  1   |    test1    |         1         |      1526793203   |
    |  2   |    test2    |         2         |      1526793203   |
    |  3   |    test3    |         3         |      1526793203   |
    +------+--------------+------------------+-------------------+

estate_risk
    +------+-------------+----------------+--------------+
    | id   | estate_id   | title           | consequence |
    +------+-------------+----------------+--------------+
    |  3   |    1        |  risktitle1    | 123          |
    |  4   |    1        |  risktitle2    | 433          |
    |  5   |    1        |  risktitle3    | 523          |
    |  6   |    2        |  risktitle4    | 976          |
    |  7   |    2        |  risktitle5    | 422          |
    |  8   |    3        |  risktitle6    | 124          |
    +------+-------------+----------------+--------------+
related_estate 
    +------+-------------+----------------+--------------+
    | id   | estate_id   | title           | storage      |
    +------+-------------+----------------+--------------+
    |  3   |    1        |  testdata      | 1             |
    |  4   |    1        |  testdata2     | 2             |
    |  5   |    1        |  testdata3     | 3             |
    |  6   |    2        |  testdata4     | 4             |
    |  7   |    2        |  testdata5     | 5             |
    |  8   |    5        |  testdata6     | 6             |
    +------+-------------+----------------+---------------+

And some other tables...(foreign all tables is estate_id) I want to get all the data in a row,in other words for example, I have several related data in the estate_risk table and when I use join query get this result data :

sample query one join:

SELECT R.id,R.title,C.title,C.storage FROM estate R LEFT JOIN estate_risk as C ON estate_risk .estate_id = R.id

result :

----------+------------------+----------------+--------------+
   R.id   |      R.title     |   C.title      |  C.consequence  
----------+------------------+----------------+--------------+
    1     |         test1    |   risktitle1   |  123         |
    1     |         test1    |   risktitle2   |  433         |
    1     |         test1    |   risktitle3   |  523         |
    2     |         test2    |   risktitle4   |  976         |
    2     |         test2    |   risktitle5   |  422         |
    3     |         test3    |   risktitle6   |  124         |
----------+------------------+----------------+--------------+

Everything is right but i want get all the data in a row that's mean only a row R.id with all C.title in one row My main goal is to display the page search results just one estate with all other data tables

+------+-------------+----------------+----------------+
  id   | estate_title| estate_risk    | related_estate |
+------+-------------+----------------+----------------+
  1        test1          risktitle1       testdata1  
                          risktitle2       testdata2      
                          risktitle3       testdata3      
---------------------------------------------------------
2 ...

Maybe my goal is not right, but I do not know how I can get this output And this should be done on the database side, or programming side ? What ideas can be made on the programming side?

</div>

Answer 1

It looks like what you want is a GROUP_CONCAT to put together the risktitles and related_estates.

GROUP_CONCAT(C.title SEPARATOR '
'),
GROUP_CONCAT(Some_other_column SEPARATOR '
')

Once you have your group functions, I believe grouping by R.id as Yanet suggested and the data should come together correctly.

If you do have larger data sets though, there are limitations to MySQL's concatenation functions. So in that case you might need to return the data as you are already and then reformat it to match what you want in the PHP side.

Group Concat Limit