SQL: select random row from table where the ID of the row isn't in another table?

How about working from this random solution.

Up vote 0 down vote favorite share g+ share fb share tw.

Q=cache:jan.kneschke.de/projects/mysql/order-by-... 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; sql random row link|improve this question edited Mar 26 '10 at 20:51Andomar56.2k44178 asked Mar 26 '10 at 20:13johnrl12728 73% accept rate.

How about working from this random solution: SELECT TOP 1 * FROM urls WHERE (SELECT COUNT(*) FROM urlinfo WHERE urlid = urls. Urlid) = 0 ORDER BY NEWID().

1 SQL does not have a == operator; MySQL does not have a NEWID() function; and TOP 1 won't work in MySQL either :) – Andomar Mar 26 '10 at 21:01 My bad on the == operator, original question never explicitly mentioned mysql, I may have missed the references to InnoDB on my first look at the question – LorenVS Mar 29 '10 at 18:29.

You need to first do a left outer join to get the set of records in 'urls' that are not in 'urlinfo', then pick a random record from that set. SELECT * FROM urls LEFT OUTER JOIN urlinfo ON urls. Urlid = urlinfo.

Urlid WHERE urlinfo. Urlid IS null Now pick a random row from this set - you can do something like SELECT newUrls. Url FROM ( SELECT urls.

Urlid, urls. Url FROM urls LEFT OUTER JOIN urlinfo ON urls. Urlid = urlinfo.

Urlid WHERE urlinfo. Urlid IS null ) as newUrls WHERE urls. Urlid >= RAND() * (SELECT MAX(urlid) FROM urls) LIMIT 1 However, this will only work if the urlids in urlinfo are roughly randomly distributed across the range of possible values.

1 Your subquery needs an alias, and * gives a duplicate column name error. But otherwise nice answer :) – Andomar Mar 26 '10 at 20:53 Thanks. Edited to fix both (I hope - I don't have a test mysql db handy at the moment) – David Mar 26 '10 at 21:05 Get VMWare :) MySQL says ERROR 1146 (42S02) at line 20: Table 'newUrls' doesn't exist, and select urls.

Url should be newUrls. Url :) – Andomar Mar 26 '10 at 21:13.

You could use where not exists to exclude rows that are in the other table. For a random row, one option is a order by rand() with a limit 1: SELECT url FROM urls WHERE NOT EXISTS ( SELECT * FROM urlinfo ui WHERE ui. Urlid = urls.

Urlid ) ORDER BY RAND() LIMIT 1.

Order by rand() limit 1" is the best way of picking a random row if you have a small table ( – David Mar 26 '10 at 21:08 Well on my machine, generating 100,000 random numbers and picking the lowest is faster than running SELECT MAX(urlid) FROM newUrls. – Andomar Mar 26 '10 at 21:38.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.