Hello i working with multiple tables(around 40) and i make a left Join for link multiple tables, the problem is in some of those tables there are multiple matches and for this rason get many rows, them i look a method for get the result more organized, because this result is sended to Javascript and the result is many tables, and many columns with null value .
MainTbl
+-------------------+
| id columA columB |
+-------------------+
| 1 data data |
| 2 data data |
| 3 data data |
| 4 data data |
+-------------------+
Table1
+-------------------+
| id columA columB |
+-------------------+
| 1 data data |
| 1 data data |
| 1 data data |
| 2 data data |
+-------------------+
Table2
+-------------------+
| id columA columB |
+-------------------+
| 2 data data |
| 2 data data |
| 1 data data |
| 1 data data |
+-------------------+
and i make a left join like this:
SELECT blah,blah,blah
FROM MainTbl
LEFT JOIN Table1 ON Table1.Id = MainTbl.Id
LEFT JOIN Table2 ON Table2.Id = MainTbl.Id
...
...
..
LEFT JOIN Table40 ON Table40.Id = MainTbl.Id
Other option i see is make a select for each ID, and organize it by table, example
select * from table1 where id='1', this result i can concat with table2,table3...table40, after concat subtables i can concat with main table, the result can be like this.
Maintbl[
MainA:'data',
MainA:'data',
Table1:[
items:[
subT1:[
Sub_subT1:data,
Sub_subT1:data
],
subT1:[
Sub_subT1:data,
Sub_subT1:data
],
subT1:[
Sub_subT1:data,
Sub_subT1:data
]
]
],
Table2:[
items:[
subT2:[
Sub_subT2:data,
Sub_subT2:data
],
subT2:[
Sub_subT2:data,
Sub_subT2:data
],
subT2:[
Sub_subT2:data,
Sub_subT2:data
]
]
]
]
but I see a problem "resource consumption", because i need to work with 40 tables, and that is 40 selects, i dont know if this method is better o not, but i no see other solution for this problem.
