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