Застрял с выборкой: 3 таблицы + many-to-many

[Sync]

Застрял с выборкой: 3 таблицы + many-to-many

Суть вроде проста. Есть 3 таблицы: таблица номеров (rooms), таблица возрастных категорий (ages) и таблица связки номеров и возр. категорий (rooms_ages) - то есть, какие возрастные категории могут размещаться в номерах.

Таблица rooms:

+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| room_id      | int(11)               |      | PRI | NULL    | auto_increment |
| name         | varchar(255)          |      |     |         |                |
+--------------+-----------------------+------+-----+---------+----------------+

Таблица ages (здесь age_start, age_end - возраст от и до):

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| age_id    | int(11)     |      | PRI | NULL    | auto_increment |
| age_start | tinyint(4)  |      |     | 0       |                |
| age_end   | tinyint(4)  |      |     | 0       |                |
+-----------+-------------+------+-----+---------+----------------+

И таблица связки rooms_ages:

+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| room_id  | int(11) |      | MUL | 0       |       |
| age_id   | int(11) |      | MUL | 0       |       |
+----------+---------+------+-----+---------+-------+

На входе имеем массив возрастов (количество неизвестно), на пальцах пусть 16 и 25 лет.
Нужно отобрать все комнаты, которые могут принять этих двух поселенцев.

Полдня попыток приводили к каким-то запросам, но не совсем верным. На чем остановился:

SELECT r.* , COUNT(*) AS total
FROM rooms r, ages a1, ages a2, rooms_ages ra1, rooms_ages ra2

WHERE
(
     r.room_id = ra1.room_id
     AND ra1.age_id = a1.age_id
     AND (
          a1.age_start <=16
          AND a1.age_end >=16
          )
)

AND

(
     r.room_id = ra2.room_id
     AND ra2.age_id = a2.age_id
     AND (
          a2.age_start <=25
          AND a2.age_end >=25
          )
)

GROUP BY room_id

То есть кол-во таблиц (aN, raN) == количеству поселенцев.

Среди результатов запроса есть правильные данные, но есть и левые, причем total по логике не совсем верный выходит.
Вобщем что-то не так, а что - не пойму.

Буду ОЧЕНЬ благодарен за помощь. Если нужны дампы - выложу.

 

[partizan]

SELECT * FROM rooms
WHERE not exists
(
 SELECT * FROM ages
 WHERE ((ages.age_start>= 16 and ages.age_end<=16) OR 
 (ages.age_start>= 25 and ages.age_end<=25) OR ...)
  AND not exists
 (
  SELECT * FROM rooms_ages
  WHERE rooms_ages.room_id = rooms.room_id and    rooms_ages.age_id = ages.age_id
 )
)

-~{}~ 07.08.06 20:46:

Логика такая:
Выбираем все комнаты, для которых НЕ существует такой возрастной категории, которая есть в списке, но которой нет в списке допустимых для данной комнаты

 

[Sync]

Ухх ) железная логика, надо переварить.
Вообще хотел обойтись без вложенных запросов. спасибо

-~{}~ 07.08.06 21:53:

Проблема вот еще какая, можно было бы отобрать age_id по возрастам и потом заюзать IN (), но там свои заморочки...
У возрастных категорий есть еще поле hotel_id, то есть для каждого отеля они свои, и ситуация типа:

age_start age_end hotel_id
12 15 1
12 16 2
12 15 3

не исключена. Кстати из-за этого и возникают трудности.
В первом посте упустил это для простоты.

 

[chira]

Sync

если поселенцы должны жить в одной комнате

SELECT r.* 
FROM rooms r, ages a, rooms_ages ra
WHERE r.room_id = ra.room_id 
   AND ra.age_id = a.age_id 
   AND 16 BETWEEN ages.age_start AND ages.age_end
   AND 25 BETWEEN ages.age_start AND ages.age_end
.... -- сколько нужно возрастов

если для каждого возраста нужно определить свои комнаты ... не вижу смысла, тогда объясни ещё раз ...

 

[Alkinoy]

Вопрос - а нафига так надо было делать? ИМХО надо было просто в базе номеров добавить2 поля - старт и енд.....