I am stuck with a case and unable to get the desired result from my query..
I have to insert into a table some records based on multiple records from 2 different tables..
A player is supposed to play a list which contains a list of items; A list needs to be played over a map.
The map contains placeholders with their certain X and Y positions and I need to place list_items behind those placeholders. for that I have created the target table that will contain the list_items positioned randomly behind the placeholders.
Below is the query in which I was able to dump the list_items for a user into the target table but, now the problem is that how to get X and Y positions randomly for each list_items from the source2 table.
My First Query:
INSERT INTO player_list_items (player_list_list_id, player_list_player_id, player_list_item_id, player_list_item_cellX, player_list_item_cellY) SELECT li.list_item_list_id, 584488596, li.list_item_item_id, 2, 5 FROM list_items li WHERE li.list_item_list_id = 2
Each list contains multiple items so the above query returns multiple items and its working fine, what needs to be considered is that the map_details table also contains multiple place holders and they would be more than the # of list_items for sure.
The required query is supposed to get all positions X and Y from the map_details table and assign then randomly to each individual item, positions for every item must be unique.
I have seen examples of multiple selects for insert but they are for single row insertion and my problem is multiple rows from multiple tables and random too.
My 2nd query:
INSERT INTO player_list_items (player_list_list_id, player_list_player_id, player_list_item_id, player_list_item_cellX, player_list_item_cellY)
SELECT list_item_list_id, 656328662, list_item_item_id, game_map_details.cellX, game_map_details.cellY FROM list_items JOIN (SELECT * FROM game_map_details WHERE map_id = 1 ORDER BY RAND()) AS game_map_details WHERE list_item_list_id = 2 GROUP BY list_item_item_id
This do adds the data from the map_details table but the data is not random, in fact its inserting only 1 pair of record for all 4 records from list_items table