SELECT query and sub-select query with dynamic table names

Question

I have an interesting situation about my MySQL db structure.

Let's assume I have three database tables: table_Main, table_sub_A, table_sub_B

"table_Main" fields:

RowID
Code (this field is used to find the "table_sub_" table
Name

Example "table_Main" rows:

1,A,Test A
2,B,Test B

Now, I want to build an SQL query which will retrieve all rows from table_Main but also get count(*) from corresponding tables for each table_Main row.

SELECT *, (SELECT COUNT(*) FROM table_sub_XXXX) AS TotalRecords  FROM table_Main

Problem starts here. XXXX should be the "Code" column value for each corresponding table_Main row. How can I generate a dynamic table name inside sub-select query?

Thanks for your help.

gbn · Accepted Answer · 2012-11-08 14:11:52Z

1

You can't

SELECT
   *,
   (SELECT COUNT(*) FROM table_sub_A) AS TotalRecordsA, 
   (SELECT COUNT(*) FROM table_sub_B) AS TotalRecordsB
FROM
   table_Main

However, MySQL may evaluate this per row of table_Main

So I'd do this to help the query optimiser

SELECT
   *, TotalRecordsA, TotalRecordsB 
FROM
   table_Main
   CROSS JOIN
   (SELECT COUNT(*) AS TotalRecordsA FROM table_sub_A) A
   CROSS JOIN
   (SELECT COUNT(*) AS TotalRecordsB FROM table_sub_B) B

answered Nov 8, 2012 at 14:11

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cem Over a year ago

Thanks but there are 500+ tables that are linked to the main table based on "Code" column on table_main. Therefore, table name on sub-select query must be dynamically set. Any suggestions?

gbn Over a year ago

@Cem: not really. You'd have to PREPARE a dynamic SQL statement by querying INFORMATION_SCHEMA.TABLES. I leave that as an exercise for you...

Collectives™ on Stack Overflow

SELECT query and sub-select query with dynamic table names

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related