SQL Searching entries not yet grouped

Question

I have two tables:

+------- IPs ------+  +---- Groups ---+
| Id (Int)         |  | Id (Int)      |
| IP (String)      |  | Name (String) |
| Comment (String) |  | IPs (String)  |
+------------------+  +---------------+

The IPs-column of Groups is a comma-separated list of the IPS that this group consists of (The Id of the IP in the IPs-table of course).

I now want to find all IPs that have not yet been grouped -> No reference in any Groups IPs-field.

My idea was to somehow join all the Groups.IPs together and then search for all the IPs.IPs that are not in that list.

I'd really love to get that done completely in SQL (Programmatically is not the problem).

Answer 1

I'm sure there's some hacky solution to your problem, but if you want to "get that done completely in SQL", I suggest refactoring your tables to avoid the comma-separated list of Ids. For a list of problems that comma-separated fields attract, I suggest checking out this recent Stack Overflow post:

• Is storing a comma separated list in a database column really that bad?

I suggest a table schema that looks something like this:

CREATE TABLE ips (
   id int,
   ip varchar(255),
   comment varchar(255),
   PRIMARY KEY (id)
);

CREATE TABLE groups (
   id int,
   name varchar(255),
   PRIMARY KEY (id)
);

CREATE TABLE ip_groups (
   group_id int,
   ip_id int,
   PRIMARY KEY (ip_id, group_id),
   FOREIGN KEY (group_id) REFERENCES ips(id),
   FOREIGN KEY (ip_id) REFERENCES groups(id)
);

Then your query could be as easy as this:

SELECT * 
FROM   ips
WHERE  NOT EXISTS (SELECT id 
                   FROM ip_groups 
                   WHERE ip_groups.ip_id = ips.id);

Example:

INSERT INTO ips VALUES (1, '127.0.0.1', 'localhost');
INSERT INTO ips VALUES (2, '10.0.0.1', 'router');
INSERT INTO ips VALUES (3, '10.0.0.100', 'my macbook');

INSERT INTO groups VALUES (1, '10.0.0 subnet');
INSERT INTO groups VALUES (2, 'computers');

INSERT INTO ip_groups VALUES (1, 2);
INSERT INTO ip_groups VALUES (1, 3);
INSERT INTO ip_groups VALUES (2, 3);

Result:

+----+-----------+-----------+
| id | ip        | comment   |
+----+-----------+-----------+
|  1 | 127.0.0.1 | localhost |
+----+-----------+-----------+
1 row in set (0.00 sec)

The 127.0.0.1 IP does not belog to any group.

---

UPDATE:

You can also solve the above by using the "null-self-join" method:

SELECT     ips.id, ips.ip, ips.comment
FROM       ips
LEFT JOIN  ip_groups ON (ip_groups.ip_id = ips.id)
WHERE      ip_groups.group_id IS NULL;

This is normally faster than the previous solution, because it does not use a correlated subquery. The problem with correlated subqueries is that they are executed once for each row in the outer query, and therefore performance suffers when the outer query (SELECT * FROM ips in this case) returns many rows.