Hi,

Forgive my terminology if I get it wrong as I'm just teaching myself SQL. I have the following query:
Code:
SELECT ROOMS.ROOMNUMBER AS 'Room Number',
       ROOMS.ROOMSTATUS AS 'Status of Room',
       FOLIOHD.CHECKIN AS 'Current C/In Date',
       FOLIOHD.CHECKOUT AS 'Current C/Out Date',
       ROOMS.LASTOCCDATE AS 'Last C/Out Date',
       FOLIOHD.HOUSENOTE AS 'Housekeeping Note',
       ROOMBOOK.FROMDATE AS 'Next C/In Date',
       ROOMBOOK.TODATE AS 'Next C/Out Date'
 
FROM ROOMS
LEFT OUTER JOIN FOLIOHD ON ROOMS.ROOMNUMBER = FOLIOHD.ROOMNUMBER
LEFT OUTER JOIN HISTHD ON ROOMS.LASTFOLIO = HISTHD.NUMBER
JOIN ROOMBOOK ON ROOMS.ROOMNUMBER = ROOMBOOK.ROOMNUMBER
 
WHERE ROOMS.ROOMNUMBER <> '103' AND  ROOMS.ROOMNUMBER <> '110' AND  ROOMS.ROOMNUMBER <> '210' AND  ROOMS.ROOMNUMBER <> '227' AND  ROOMS.ROOMNUMBER <> '228'
which is working except it returns a separate row for each value in ROOMBOOK.FROMDATE, so I get something like this:
Code:
Room Number	Status of Room	Current C/In Date	Current C/Out Date	Last C/Out Date	Housekeeping Note	Next C/In Date	Next C/Out Date
104	O	26/12/12	27/12/12	26/12/12		27/12/12	01/01/13
104	O	26/12/12	27/12/12	26/12/12		02/01/13	04/01/13
104	O	26/12/12	27/12/12	26/12/12		04/01/13	05/01/13
104	O	26/12/12	27/12/12	26/12/12		05/01/13	19/01/13
104	O	26/12/12	27/12/12	26/12/12		19/01/13	06/04/13
104	O	26/12/12	27/12/12	26/12/12		06/04/13	13/04/13
104	O	26/12/12	27/12/12	26/12/12		26/04/13	28/04/13
104	O	26/12/12	27/12/12	26/12/12		19/07/13	20/07/13
104	O	26/12/12	27/12/12	26/12/12		11/08/13	12/08/13
105	V			24/12/12		26/12/12	28/12/12
105	V			24/12/12		28/12/12	29/12/12
105	V			24/12/12		29/12/12	30/12/12
105	V			24/12/12		30/12/12	01/01/13
105	V			24/12/12		02/01/13	04/01/13
105	V			24/12/12		05/01/13	19/01/13
105	V			24/12/12		19/01/13	06/04/13
105	V			24/12/12		06/04/13	13/04/13
105	V			24/12/12		26/04/13	28/04/13
105	V			24/12/12		19/07/13	20/07/13
105	V			24/12/12		11/08/13	12/08/13
106	V			26/12/12		26/12/12	27/12/12
106	V			26/12/12		27/12/12	01/01/13
106	V			26/12/12		02/01/13	04/01/13
106	V			26/12/12		11/01/13	19/01/13
106	V			26/12/12		19/01/13	06/04/13
106	V			26/12/12		06/04/13	13/04/13
I only want it to return the earliest value for ROOMBOOK.FROMDATE (ie: the next check/in, not all future checkins). How do I filter for this?

Thanks,