Skip to content
Advertisement

How To Output results from a table where the data does not exist in the other table MYSQL

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
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement