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..

you have a different table for each member? Sounds like a bad design. If you need to perform a loop, take a step back and reconsider your table design. You should not have to loop to get data from your database. — Taryn
– Taryn, Commented Oct 11, 2012 at 11:54
I know but for some reasons i made it like that... I cant change it now — DomincJune
– DomincJune, Commented Oct 11, 2012 at 11:55
If you can't change it then your database is probably doomed. — Clodoaldo Neto
– Clodoaldo Neto, Commented Oct 11, 2012 at 12:11

Taryn · Accepted Answer · 2012-10-11 12:40:43Z

2

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;

edited Oct 11, 2012 at 12:40

answered Oct 11, 2012 at 11:58

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

6 Comments

eggyal Over a year ago

Why use a string (rather than an integer) type for the literal Member column?

Clodoaldo Neto Over a year ago

Although your solution works, giving the OP any hope on his failed attempt is counter productive.

Taryn Over a year ago

@Clodoaldo I completely agree, the current table structure is awful.

DomincJune Over a year ago

HI bluefeet thanks for the suggestion and help... Apologies if i am not clear in my question because i dont have a table named member..but it have different table for each member like d1_gps for 1 and d2_gps for 2 and so on.. SO i m thinking to fetch all the members of a team in as array and use this for querying... am i doing it right

Taryn Over a year ago

@DomincJune You can try to do it that way but your design is incredibly bad and is going to continue to cause you problems to query.

|

solaimuruganv · Accepted Answer · 2012-10-11 12:22:12Z

0

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

answered Oct 11, 2012 at 12:22

solaimuruganv

30.3k1 gold badge22 silver badges23 bronze badges

Collectives™ on Stack Overflow

Is it possible to implement a loop in mysql query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related