CODEWITHSUNDEEP
mysqlsql
Cem
Cem

Reputation: 179

SELECT query and sub-select query with dynamic table names

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:

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.

Upvotes: 0

Views: 557

Answers (1)

gbn
gbn

Reputation: 432281

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

Upvotes: 1

Related Questions