Help me. I have the following tables as an example, but actually there are many fields here just put 3 id(20,25,35). Clarified this, let's go to the example: I have table1, which has id and name of table_2 as shown below:
How do I group all the fields except id_table2 and name. the result that I would need would be: that for each id of table 1 it shows me all the id and name of table 2
here my query:
SELECT t1.*, t2.id, t2.nombre
FROM tabla_1 AS t1
INNER JOIN t2 AS t2 ON t2.id_tabla2 = t1.id
I suppose that from how it looks in your expected response, you require that only "the header" of table_a be displayed, in that case an example that I can give you is the following:
This is a replica of your example (made in MSSQL, the other scripts are the ones that work in MySQL version 8.0 and up):
To do this, I first do a
row_number
to identify the header of table_a and thus only show that row and omit the rest (by omitting I do not mean the data, but the visualization of the same), this as it is would not work so I I put in a CTE:Finally we make use of the created CTE and use
case...when
it to show only the headers, that is, those rows that have 1 in the nroFila field:The final output would be as follows:
I hope you can apply it as you see fit, greetings!
If what you need is a JSON, you can generate it with MySQL 8's own functions:
It should result in a more manageable JSON: