Mysql – Help with a complicated MySQL Query inserting data using select from 2 tables

insertjoin;MySQLquery

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

Tables: Target: player_list_items Source1: list_items Source2: map_details

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.

enter image description here

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

enter image description here

Best Answer

Okay its done, the cursor usage worked

here is the code m using 

UPDATE player_list_items SET player_list_item_cellX = cell_X, player_list_item_cellY = cell_Y
             WHERE player_list_id IN (
                 SELECT player_list_id FROM (
                 SELECT player_list_id FROM player_list_items 
                 WHERE player_list_player_id = playerID AND player_list_list_id = @listID AND player_list_item_cellX IS NULL
                 ORDER BY player_list_id LIMIT 1
                 ) tmp
             );

please let me know if this solution is not a good one... as I found nothing besides this to update multiples rows with different values..

its in a loop against cursor that have all the map_details data needs to be put in this table

Related Question