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…
x
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