I have a table which places a client on a ‘grid’ with x and y coordinates from -10 to 10.
I need to select the grid sections which there are no clients. I have tried using NOT EXIST but it does not show me the data I need. i.e if empty gird section was at -1-8 it would not select it.
I have tried this way below:
Also I know this was not a well worded question but would appreciate any tips on how to rod it better…
SELECT *
FROM grid
WHERE NOT EXISTS (
SELECT 1
FROM client
WHERE client.x = grid.x
ANDclient.y = grid.y
);
Here is a SQLFiddle to show my environment and tables.
Advertisement
Answer
The grid table needs to have a row for each point in the grid. You only have rows for the points on the diagonals.
INSERT INTO grid VALUES (1, -10, -10), (2, -10, -9), (3, -10, -8), ... (11, -10, 0), ... (21, -10, 10), (22, -9, -10), (23, -9, -9), ...
If you make the id column AUTO_INCREMENT, you can automate this with the following query:
WITH numbers AS
SELECT -10 AS x
UNION
SELECT -9
UNION
SELECT -8
UNION
...
UNION
SELECT 0
UNION
SELECT 1
...
UNION
SELECT 10) AS numbers
INSERT INTO grid (x, y)
SELECT n1.x, n2.x
FROM numbers
CROSS JOIN numbers