Mysql left join count

Question

My question relates to a game I'm writing in the game items, the badguys bodies, and coins drop on the ground at a specific x,y coordinate of a land when a group of items is on a tile the game will show a treasure icon. its important that I count how many so when a player picks them up it will be removed if the player takes them all. There are other ways to do this but the method I'm using suits my application well.

So I need to join 3 tables on fields x, y, land and count the number of occurances of each combination and coins are stored as their value so I would like to count each coin (in other words add value to count) so I have tables as follows in all tables posx,posy, land are grounped as table name: game_coins fields: posx, posy, land, value <-- counts as # stored in value

table name: game_items
fields: posx, posy, land, item_id   <--counts as 1


table name: game_corpses
fields: posx, posy, land, id <-- counts as 1

Here is the query I used so far I get an ambiguous error and I know what that means but it seems like I should be able to call all the tables with 1 column name if they are the same and I'm grouping by that column

SELECT  posx, posy, land, COUNT(*) as 'count' 
FROM game_itemlist  
LEFT JOIN game_corpses ON posx, posy, land  
LEFT JOIN game_coins ON posx,posy,land 
WHERE land = 'test' GROUP BY posx, posy, land

I could add value to count afterwords but I can't get the left join correct.

Answer 1

This isn't really a JOIN. It's not that each game_item is related to a specific set of game_corpses; it's just two sets of things you're counting.

A UNION may be more appropriate:

SELECT COUNT(1) FROM
(
 SELECT * FROM game_items WHERE ...
 UNION
 SELECT * FROM game_corpses WHERE ...
)

Depending on how much of this you're doing you may benefit by consolidating these two tables into one with a field indicating whether it's an item or a corpse.