How to get random number based on the condition.

[pollysal]

i have two table in my database which is the room table and roomtype table.i'm using phpmyadmin.

 

room table

room_no

r_roomtypeID

 

 

roomtype table

roomtypeID

roomtype

 

 

how can i create a query that can select A random room_no from "room table "

based on the roomtypeID in the "table roomtype"

 

p/s - each roomtypeID got it's own quantity room no.

 

              roomtypeID    room_no

 

example  : Single room  : 1-10

      :    Deluxe room  : 11-20

      :  Suite room  : 21-30

 

right now i'm only having this kind of idea. the random number from room. I don't know how can i generate A random room_no from table room based on the roomtypeID.

(SELECT room_no FROM room ORDER BY RAND( )LIMIT 1) 

 

any ideas is really appreciated:)

[ignace]

SELECT * FROM room WHERE r_roomtypeID = $rtID ORDER BY rand() LIMIT 1

 

or

 

SELECT * FROM room r, roomtype rt WHERE rt.roomtypeID = r.r_roomtypeID AND rt.roomtypeID = $rtID ORDER BY rand() LIMIT 1

 

I prefer the first