1

I want to show the data of more than 50 tables in one list view. I have formed the query below. Left join is dynamically added using PHP forloop. Any other ways to get values from various tables(more than 50 tables)? Or please suggest any other technology to use PHP Product...

SELECT * FROM main_table `t`
LEFT JOIN `table` ON table.c_application_id = t.id
LEFT JOIN `table1` ON table1.c_application_id = t.id
LEFT JOIN `table2` ON table2.c_application_id = t.id
LEFT JOIN `table3` ON table3.c_application_id = t.id
LEFT JOIN `table4` ON table4.c_application_id = t.id
LEFT JOIN `table5` ON table5.c_application_id = t.id
LEFT JOIN `table6` ON table6.c_application_id = t.id
LEFT JOIN `table7` ON table7.c_application_id = t.id
LEFT JOIN `table8` ON table8.c_application_id = t.id
LEFT JOIN `table9` ON table9.c_application_id = t.id
LEFT JOIN `table10` ON table10.c_application_id = t.id
LEFT JOIN `table11` ON table11.c_application_id = t.id
LEFT JOIN `table12` ON table12.id=t.c_cycle
LEFT JOIN `table13` ON table13.c_groupId=t.c_program_to_apply
LEFT JOIN `table14` ON table14.id = table14.c_status_id 
LEFT JOIN `table15` ON table15.id = table15.c_stage_id 
LEFT JOIN `table16` ON table16.c_code = table16.c_country 
LEFT JOIN `table17` ON table17.id=table17.c_app_form
Muhammad Dyas Yaskur
  • 6,914
  • 10
  • 48
  • 73
user3331175
  • 31
  • 5
  • 1
    So you basically read the complete DB? – juergen d Apr 11 '17 at 09:21
  • `INNER` joins would be quicker if you only want cases where the join can be made. Indexing your foreign keys (`c_application_id`) on the tables you are joining to would also help. plenty of resource on how to do that. one example of many : http://stackoverflow.com/questions/3002605/how-do-i-add-indexes-to-mysql-tables – Jason Joslin Apr 11 '17 at 09:23
  • Why are there so many different tables (`table` to `table11`) since they (apparently) have the same structure? In this case the data should be stored in a single table. – Jocelyn Apr 11 '17 at 09:32
  • (table1 to table11) some of columns differ from each table – user3331175 Apr 11 '17 at 09:38
  • better if you create a `view` of the required fields instead of joining all the tables, its like you are querying the whole db! – Muhammad Dec 21 '19 at 05:06

0 Answers0