MYSQL, SELECT id WHERE id DOES NOT EQUAL MULTIPLE VALUES

[sloth456]

Hi guys,

 

I need to select 1 random row from a table but the row id must NOT match one of multiple values (possibly thousands).

 

To put it another way:

 

SELECT any random row EXCEPT ones WHERE id="1,6,9,100,300,56,2,44......"

 

Whats a good way to do this considering there could be more than 3,000 id's we would not want to pick from.

 

Any help would be greatly appreciated.

[WebStyles]

are those 3,000+ invalid ids also stored in a table?

 

 

[sloth456]

Actually yes they would be.

 

I can select them from a table like this

 

SELECT ids FROM reports WHERE group_id=$group_id

 

This would bring up a list of ids I would not want to come up again in my new selection.

[WebStyles]

try playing around with something like this:

 

SELECT DISTINCT table1.id

FROM table1

WHERE table1.id NOT IN (

SELECT table2.id

FROM table2);

 

[sloth456]

Hmm, I hadn't realised you could select data in this way, looks promising.  I shall try it out soon and report back.

[sloth456]

Sorry for the late reply, this worked great!  May I ask what the 'DISTINCT' bit is for, is it necessary, my query seems to work without it.

[WebStyles]

DISTINCT is to grab unique values...

[kickstart]

Hi

 

You could also use a JOIN

 

SELECT DISTINCT table1.id
FROM table1
LEFT OUTER JOIN table2.id
WHERE table2.id IS NULL

 

Used to be far more efficient to do it this way, but think with newer versions of MySQL the 2 methods are far more evenly matched.

 

All the best

 

Keith