Can I somehow combine these two queries into one in MySQL 5.7 without global variables and stored procedures?

define p1_id, p2_id int;

...

insert into Cards_in_game_decks select * from Cards_in_decks where Cards_in_decks.player_id=p1_id order by rand() limit 10;
insert into Cards_in_game_decks select * from Cards_in_decks where Cards_in_decks.player_id=p2_id order by rand() limit 10;

CodePudding user response：

You just need to do a union:

insert into Cards_in_game_decks select * from (
  (select * from Cards_in_decks where Cards_in_decks.player_id=p1_id order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=p2_id order by rand() limit 10)
) random_cards

fiddle

To get up to 10 cards per player for up to 6 players listed in a Players table, you just have to get repetitive:

insert into Cards_in_game_decks select * from (
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 0,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 1,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 2,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 3,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 4,1) order by rand() limit 10)
  union all
  (select * from Cards_in_decks where Cards_in_decks.player_id=(select id from Players where game_id=1 order by id limit 5,1) order by rand() limit 10)
) random_cards

fiddle

There are likely much better ways to do this in mysql 8.