Return to Question

I need to find a way to select one random row where the subscriber is not also leader (in the same or any other row).

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?

I need to find a way to select one random row where the subscriber is not also leader (in the same or any other row).

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?

UPDATE

I noticed a long execute time for this query (about 40 secs)!

This is the output for my table relations:

| relations | CREATE TABLE `relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leader` int(11) NOT NULL,
  `subscriber` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35207 DEFAULT CHARSET=latin1 |

I need to find a way to select all subscriber (rand and limit 1) that are not also leader.

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?

I need to find a way to select one random row where the subscriber is not also leader (in the same or any other row).

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?

I need to find a way to select all subscriber (and limit 1) that are not also leader.

I trired something like this:

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

I need to find a way to select all subscriber (rand and limit 1) that are not also leader.

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?