Jump to content
  • Advertisement
Sign in to follow this  

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

This topic is 2843 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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

FROM map AS map
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.


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
It sounds like a LEFT OUTER JOIN is being asked for. Then just add something like "WHERE table2.thing IS [NOT] NULL".

Share this post

Link to post
Share on other sites
Sign in to follow this  

  • Advertisement

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!