PDA

View Full Version : complex query help plz



hola
04-25-2007, 12:05 AM
User selects start and end date....from 25 to 27....

+---------+------+-----------+
| day_Num | qty | room_Type |
+---------+------+-----------+
| 25 | 2 | single |
| 25 | 0 | double |
| 25 | 0 | ensuite |
| 26 | 6 | single |
| 26 | 13 | double |
| 26 | 5 | ensuite |
| 27 | 1 | single |
| 27 | 1 | double |
| 27 | 1 | ensuite |
+---------+------+-----------+

room_Type = double and ensuite has ZERO qty....so all the related data with double and ensuite...should not be printed...so the OUTPUT should be:

+---------+------+-----------+
| day_Num | qty | room_Type |
+---------+------+-----------+
| 25 | 2 | single |
| 26 | 6 | single |
| 27 | 1 | single |
+---------+------+-----------+

And from here then... the lowest qty should be printed out, so the FINAL OUTPUT should be:

+---------+------+-----------+
| day_Num | qty | room_Type |
+---------+------+-----------+
| 27 | 1 | single |
+---------+------+-----------+

Anyone know what the query should be?

codeexploiter
04-25-2007, 04:14 AM
Try the following query



SELECT *
FROM rooms
WHERE room_type NOT
IN (
'double', 'ensuite'
)
AND qty = (
SELECT MIN( qty )
FROM rooms
WHERE room_type NOT
IN (
'double', 'ensuite'
)
GROUP BY day_num
ORDER BY qty ASC
LIMIT 0 , 1 )
ORDER BY qty
LIMIT 0 , 1;



Make sure that the that you check the table name and field names hat I've mentioned in the query is correct to your table/database.