Sign in to follow this  
menyo

[web] mysql, select when no match in other table.

Recommended Posts

Hey guys,

I have a table for my map with coordinates and a table with stuff on the map. Now i want to randomly place an item on a tile where there is nothing yet.

So i thing i need to do some join, but i'm still strugling with all those different joins and how to exactly implement them.

Let's say this:

Map: map_ID, x, y, type

item: item_ID, x, y

now, like i said, i want to add an item on a random place where there is no item yet.

I could select the X an Y for all the items first. And subtract them from the selected map tiles then pick a random key in the array where the item should be placed.

But there must be some mysql statement that does all this work for me right?

Share this post


Link to post
Share on other sites
The simplest way is to generate the X and Y values in a loop, until no item at x,y exists. Assuming items are few and far between this should run only 2/3 quick queries at most. Obviously in the case where the map gets full the number of queries will increase alarmingly, and once it is full of items it would never stop. You can just put an arbitrary upper limit on the number of loops, such as 1,000 or whatever suits, depending on how critical item placement is to your gameplay. I somehow doubt you would want to allow that many items though, most games don't.

You can use ORDER BY RAND() and a LIMIT clause to do the random selection in SQL, but heed the warnings about performance mentioned. This could be prohibitive if your map is large.

I think for this problem keep it simple. If it turns out the simple solution isn't working well for you, you can change it later.

Share this post


Link to post
Share on other sites
Thanks but i don't think thats what i'm looking for. I need to render out all the options where there is already an item.

I tried this in million ways without succes.

SELECT map.x, map.y
FROM map
LEFT JOIN items
ON map.x = items.x AND map.y = items.y
WHERE items.x AND items.y IS NULL




But i think i fixed it now by using a if not exist inside a query.

SELECT x, y
FROM map AS map
WHERE NOT EXISTS
(
SELECT x, y
FROM items AS items
WHERE map.x = items.x AND map.y = items.y
)




I have 2 items on my map of 10000 tiles and num rows returns 9998, so this is what i am looking for but i need to refine it a bit more for my usage.

Share this post


Link to post
Share on other sites
I would consider changing your schema such that a map tile has an item. You get the location of the item based on the map tile it is associated with, it doesn't have it's own set of coordinates.

Map: MapId, X, Y, ItemId

Item: ItemId, Name, etc...

This would restrict it to one item per tile, if that's a problem you could just use a mapping table to map items to tiles.

This could simplify your query considerably and would offer much better performance than the NOT EXISTS example you have now.

SELECT * FROM Map WHERE ItemId IS NULL LIMIT 1 ORDER BY RAND();


If you're doing this update often, you should probably pull out the list of empty map tiles and work with them in code.

If you're determined to keep your existing layout, here is a post with some helpful comments on how to do the sort of join you're looking for. SQL Outer Join vs. Where Not In vs. Not Exists.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this