Is it possible to implement a loop in mysql query

Question

Hi there i have a table:

Teams: as below

|Team ID  | Member ID|
| 1       |  10       |
| 2       |  230      |
| 1       |  11       |
| 4       |  56       |
| 1       |  15       |
| 2       |  236      |
| 1       |  18       |
| 3       |  43       |
| 1       |  17       |

I did this to find the members of a team:

SELECT members from teams where team_ID = 1; and it gave me 10,11,15,18,17

I have a different table for each member .So I tried something like this to fetch the data from different tables and it worked fine:

SELECT * FROM 10
UNION ALL 
SELECT * FROM 11
UNION ALL 
SELECT * FROM 15
UNION ALL 
SELECT * FROM 18
UNION ALL 
SELECT * FROM 17

Is it possible to make this 2 Queries into 1 Query because the members of a team changes dynamically....

Any help please...

Let me be bit more clear: My final result should only contains data from different member tables. Hope I am clear..

Answer 1

First, I strongly advise that you consider a redesign of your table structure otherwise you will have a difficult time querying this data.

You can do something like this though. Of course, this only works if you know the tables member tables that you want to query:

select *
from teams t
left join
(
  SELECT *, 10 Member 
  FROM 10
  UNION ALL 
  SELECT * , 11 Member 
  FROM 11
  UNION ALL 
  SELECT * , 15 Member 
  FROM 15
  UNION ALL 
  SELECT * , 18 Member 
  FROM 18
  UNION ALL 
  SELECT * , 17 Member 
  FROM 17
) m
  on t.memberid = m.member

If you want to do this dynamically, then you might be able to use a prepared statement similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select *, ',
      MemberID,
      ' AS Member from ', MemberID, '
       '
    )
  separator ' union all ') 
  INTO @sql
FROM teams
WHERE TeamID = 1;

SET @sql = CONCAT('select *
                   from teams t 
                   left join
                   (', @sql, ' ) m
                     on t.memberid = m.member');

-- select @sql

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 2

it can be done by strored procedure or function ,

write function and get the count of the MEMBER, based on the count construct the loop will be executed use temp to stored the result.

in PostgreSQL dynamic query construct can be execute by using EXECUTE