MySQL – How to Fix Incorrect Values Returned by In Between Query

MySQL

I have a little problem right here…

So all in all I have exactly two tables: 

* resources (ID, resourcetype_ID, chunk_ID, position)

* chunks (ID, x, y)

The first one stores some rows which belongs to the certain chunk.
The second one just stores all the chunks with a different set of x and y as some kind of position.

My goal is to select all the resources that belong to a certain range of chunks.
But I have made a mistake somewhere. I always get the wrong x and y positions, when I select the resources.

SELECT resources.*, chunks.x, chunks.y 
FROM resources, chunks 
where chunk_ID in (SELECT ID 
                   FROM chunks 
                   where x between 17185-1 and 17185+1 
                     and y between 10846-1 and 10846+1) 
group by ID

This query above returns all the resources that belongs to the different chunks. But the returned x and y position is for all of them the same. Why does this happen?

(As you can see the resources with "chunk_ID" = 1 have the same x and y as the resources with "chunk_ID" = 5 … even if those chunks have completely different x and y pairs…)

enter image description here

Normally all resources should have different x and y values regarding to the chunk they belong to.

Best Answer

Your query:

SELECT resources.*, chunks.x, chunks.y 
FROM resources, chunks 
where chunk_ID in (SELECT ID 
                   FROM chunks 
                   where x between 17185-1 and 17185+1 
                     and y between 10846-1 and 10846+1) 
group by ID

I see several issues:

  • The two tables in the FROM clause are not joined (that's the reason for the irrelevant results you get, the two tables are correlated through the subquery* but the FROM clause introduces an additional non-correlated chunks reference which produces all these unrelated results):

    FROM resources, chunks

    Join them:

    FROM resources JOIN chunks ON resources.chunk_id = chunks.id

  • Since chunks is in the FROM clause, we don't need* the subquery no more, to check x and y:

    where chunk_ID in (SELECT ID 
                   FROM chunks 
                   where x between 17185-1 and 17185+1 
                     and y between 10846-1 and 10846+1)

    Simplify WHERE:

    WHERE chunks.x BETWEEN 17185-1 AND 17185+1 
  AND chunks.y BETWEEN 10846-1 AND 10846+1

  • You have SELECT * combined with GROUP BY. This is a recipe for spurious, wrong results.

    Just remove the GROUP BY.

The query becomes after using all the above comments (and some polishing with table aliases):

-- Suggested query
SELECT r.*, c.x, c.y 
FROM resources AS r
     JOIN chunks AS c
     ON r.chunk_id = c.id  
WHERE c.x BETWEEN 17185-1 AND 17185+1 
  AND c.y BETWEEN 10846-1 AND 10846+1 ;

* Just for completeness, we could have kept the IN subquery and remove chunks from the FROM clause.

This would have the restriction that we couldn't have any column from chunks in the SELECT list - and more likely worse performance than the JOIN query:

-- Don't use this one
SELECT r.* 
FROM resources AS r
WHERE r.chunk_d IN 
      ( SELECT c.id
        FROM chunks AS c
        WHERE c.x BETWEEN 17185-1 AND 17185+1
          AND c.y BETWEEN 10846-1 AND 10846+1
      ) ;
Related Question