SQL: select random row from table where the ID of the row isn't in another table?
- by johnrl
I've been looking at fast ways to select a random row from a table and have found the following site: http://74.125.77.132/search?q=cache:http://jan.kneschke.de/projects/mysql/order-by-rand/&hl=en&strip=1
What I want to do is to select a random url from my table 'urls' that I DON'T have in my other table 'urlinfo'.The query I am using now selects a random url from 'urls' but I need it modified to only return a random url that is NOT in the 'urlinfo' table.
Heres the query:
SELECT url
FROM urls JOIN (SELECT CEIL(RAND() * (SELECT MAX(urlid)
FROM urls
)
) AS urlid
) AS r2 USING(urlid);
And the two tables:
CREATE TABLE urls (
urlid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
url VARCHAR(255) NOT NULL,
) ENGINE=INNODB;
CREATE TABLE urlinfo (
urlid INT NOT NULL PRIMARY KEY,
urlinfo VARCHAR(10000),
FOREIGN KEY (urlid) REFERENCES urls (urlid)
) ENGINE=INNODB;
