[SOLVED] SELECT from a table if a record doesn't exist in another?

[Strahan]

I have a table that I want to query for records that are not marked in another table.  For example, this is the source table "chars":

id [int, index]

chartxt [text]

 

Then there is the other table: "charused":

uid [int, index]

cid [int]

user [int]

 

A logged in user gets a numeric ID, say 123.  I want to get a random record from the database, but one that hasn't been used yet.  When the client uses a "character", it enters a record with the char ID into the charused table so I can keep track of what has been used and what hasn't.

 

I was thinking select chartxt from chars right outer join charused on chars.id = charused.cid where charused.user is null order by rand() limit 1

 

So it would pull a record but only if the user is null in charused, meaning no record was found there.  However, that doesn't seem to work.  Any ideas?

 

Thanks!

[rhodesa]

you were close...i think it should be a left join

select chartxt from chars left join charused on chars.id = charused.cid where charused.user is null order by rand() limit 1

 

[Strahan]

Whoo hoo, that did it!  Thanks alot, ugh, I can't believe I was pounding my head against the wall for something as simple as getting my lefts and rights mixed up

 

Thanks again!