Help pls. Find records NOT in 1 table but in another

[llandudno]

Hi folks, been away for a bit, and forgotten some things, anyhow, can you pls help.  I have 2 tables in a DB, one contains a list of "rooms' the other is for room availability. - based on date.

All I want to do is find out which rooms are available on certain dates. e.g:

$sql = "SELECT * FROM roomlist
  LEFT JOIN roomavailability ON roomavailability.roomlistID=roomlist.roomlistID
  WHERE roomavailability.datefrom BETWEEN $adate AND $departdate
  AND roomavailability.dateto BETWEEN $adate AND $departdate";
  $rs  = mysql_query($sql) or die("Error: " . mysql_error());
  while ($row = mysql_fetch_array($rs, MYSQL_ASSOC))
  {
  echo  $row['roomlistID'];
  }

This query gives me the right results for rooms that have been booked, but  what I actually want is roomlistID from roomlist if it is not in the above query.  understand?

Help much appreciated, thanks

[.josh]

this may be a real dumb answer, but you are saying you want the opposite result, right? did you try

... roomavailability.roomlistID != roomlist.roomlistID ....

?

[llandudno]

Sorry but ... roomID!=roomlist.roomlistID does not give the result I want.