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

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

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