SQL Query - display join result in one result row

Question

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?

On the Programming side, you could make a Group By R.id, for instance if you need to show them in Report — YanetP1988, May 23 '18 at 15:30
This is definitely not something to do on the database side. Get the database to get your records back, and use your display/ui side to display the data the way you wish. Also, I believe `GROUP BY r.id` on your sample SQL statement would be a BAD idea. — JNevill, May 23 '18 at 15:33
When add Group By R.id only get one result from estate_risk , like: risktitle1 but i need risktitle1,risktitle2,risktitle3 on result — lock, May 23 '18 at 15:34

score 1 · Accepted Answer · answered May 23 '18 at 15:43

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

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

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

Thanks a lot,I did the same thing in the same way – lock May 25 '18 at 09:14 — lock, May 25 '18 at 09:14

SQL Query - display join result in one result row

1 Answers1