insert into table with multiple records from multiple tables

Question

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.

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

either an insert with multiple selects or even another query {update} after this would work for me because this would run only once for a user per list.

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.

I have a Stored Procedure for this case because it has lots of other stuff thats working fine so, if anyone here want to give answer using SP that would be useful and not a problem for me to use.

Hope the above stuff is making sense and clarify my point.

UPDATE

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, 584488596, 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

Regards

Answer 1

I'm not sure I'm following, but it sounds like what you need is to join list_items to map_details. If both tables had some kind of sequence field which always starts at one and increases monotonically, without gaps, you could join them on that. Given this data:

Seq  Name
1    Barrel
2    Box
3    Crate

Seq  Position
1    13, 4
2    99, 2
3    8, 11

Then, SELECT I.Name AS ItemName, M.Position FROM Items AS I INNER JOIN Map AS M ON I.Seq = M.Seq would give you this:

ItemName Position
Barrel   13, 4
Box      99, 2
Crate    8, 11

Although you may have such a field, since you need randomization, you will need to create a new sequence for at least one of the two tables, in a subquery; a thread here discusses how to do so in MySQL. In that thread, the rows are numbered in a specific order, but you can sort by GUIDs or some other random element to get items in a random order. In MS SQL, I'd use ROW_NUMBER(), and Oracle has ROWNUM, but it looks like MySQL may be less convenient. If you find a better way, please add it to that other question for the benefit of future generations.

You will need to multiply your sequence numbers by the ratio of map locations to the number of items, so that the item numbers span the whole range of map numbers. Be sure to calculate this ratio as a FLOAT, but to convert the resulting numbers to INT so they join correctly. For example, if there are four items and ten map locations, you would randomly order the items and assign them the numbers 1, 2, 3, and 4; multiply these by 2.5 (the ratio of map locations to items), and then round these back to integers, leaving you with 2 (1 x 2.5, rounded down to 2), 5, 7, and 10. This is not perfect, as it means some map locations will come up more often than others. If you randomize the order of map locations ''and'' items, it should even out.

---

Here's an illustration of how this may work. Suppose you're using these items:

ItemID Name
42     Barrel
79     Box
101    Crate

...and you're using these map locations:

MapID Location
22    13, 4
38    99, 2
44    8, 11
45    7, 10

Start by assigning sequential numbers to each of these lists, as described in that other thread:

ItemID Name    Seq
42     Barrel  1
79     Box     2
101    Crate   3

MapID Location Seq
22    13, 4    1
38    99, 2    2
44    8, 11    3
45    7, 10    4
50    1, 12    5

You can now join Items to Map on Items.Seq = Map.Seq:

ItemID Name    Seq Location
42     Barrel  1   13, 4
79     Box     2   99, 2
101    Crate   3   8, 11

However, you'd always assign your items to the first elements from the map. To work around this, you can either randomize the order of map elements, or multiply the sequence numbers of your items by the ratio of the two counts (5/3):

ItemID Name    Seq  NewSeq
42     Barrel  1    1 * 1.6667 = 1.6667 = 1
79     Box     2    2 * 1.6667 = 3.3333 = 3
101    Crate   3    3 * 1.6667 = 5.0000 = 5

...now, the items are distributed evenly among the map positions, at 1, 3, and 5, instead of being clumped at the start of the map. Note that items will still have the same location every time, provided the item list and map list are unchanged, so you may be better off assigning random sequence numbers to your map locations instead.

Answer 2

Okay I got the answer

Used cursor for the map_details and looped through it to update the items list, here is the code just in case it helps someone or someone gives me a better idea on it.

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
             );