Please Help...

Hey All,

Is it possible to select a random record of a table where the record has no related records in an inner joined table?

ie...

[Table 1. - Main Table (members)]

------------------------------
ID | Firstname | Lastname
------------------------------
01 | James | Bond
02 | Paul | McMahon
03 | Sean | Connor
------------------------------

[Table 2. - Related Table (Orders for example)]

------------------------------
ID | Order # | Product
------------------------------
02 | ON-1457 | Cheese
02 | ON-1457 | Bread
05 | ON-1590 | Milk
------------------------------

So we know Paul has made an order (for Cheese & Bread), now how can we select (randomly) "James" or "Sean" as they have no orders?

Does this make any sense?

TIA.

ok ;)

have not a clue how succsessfull this would be as RAND() can do strange things but anyway - assuming that orders.ID is NOT NULL

SELECT members.*,orders.* FROM members LEFT JOIN orders ON orders.ID=members.ID where members.ID is NULL ORDER BY RAND();

which should select all members whose ID does not appear in orders, but no promises as I have not actually tried it!

Thanks firepages!

That was almost right, I changed one or two things and it's working now. Excellent. ;)

Regards, Dodger.