Объединить таблицы

[SilverFish]

Подскажите, пожалуйста, как правильно дописать запрос с параметром, чтобы получить результат на прилагаемой картинке.
запрос

[volvo877]

Вот так:

Код

SELECT sensors.id, sensors.ser_num, sensors.checkbool
FROM sensors, rooms, rooms_sensors
WHERE 
  rooms_sensors.id_sensors=sensors.id
  AND rooms_sensors.id_rooms=rooms.id 
  AND rooms_sensors.id_rooms=12 
  AND rooms_sensors.id_rooms IS NOT NULL AND rooms_sensors.id_sensors IS NOT NULL
 
UNION
 
  SELECT sensors.id, sensors.ser_num, sensors.checkbool
  FROM sensors
  LEFT JOIN rooms_sensors 
    ON (rooms_sensors.id_sensors = sensors.id)
  WHERE (rooms_sensors.id_sensors IS NULL OR rooms_sensors.enddata IS NOT NULL)
 
ORDER BY ser_num;

[SilverFish]

Большое спасибо! Завтра попробую.

[SilverFish]

Да, работает как надо. Можно в двух словах объяснить как работает эта строка:
WHERE (rooms_sensors.id_sensors IS NULL OR rooms_sensors.enddata IS NOT NULL)

Вверху я проверяю что AND rooms_sensors.id_sensors IS NOT NULL, а в условии WHERE уже стоит IS NULL OR.
Просто хочу разобраться с этими UNION...

[volvo877]

Можно в двух словах объяснить как работает эта строка:
WHERE (rooms_sensors.id_sensors IS NULL OR rooms_sensors.enddata IS NOT NULL)

Стандартный метод выбора всех записей из таблицы T1, которые не присутствуют в таблице T2 - вот такой:

Код

SELECT t1.name
FROM t1
LEFT JOIN t2 ON t2.name = t1.name
WHERE t2.name IS NULL

, я после UNION просто вытащил из sensors все поля, которых нет в rooms_sensors. Но только этого недостаточно, нужны же еще поля, где enddata уже задана, и сенсор свободен. Вот и добавляем это условие в WHERE: или сенсора нет в нет в rooms_sensors, или он есть, но свободен (поэтому через OR). И получаем нужный результат...

[SilverFish]

Большое спасибо за объяснения.

Не могу понять или я запуталась или что-то надо добавить в запрос.
Берем помещение Магазин цветов. У него номер 22. В таблице связей rooms_sensors видим, что магазину цветов ничего не принадлежит. Следовательно, мы должны получить только свободные sensors. Однако в выборку попал sensors с ser_num №3, а из таблицы связей видно, что этот sensors занят в помещении Вестибюль, значит этого sensors с ser_num №3 не должно быть в выборке.
Суть на картинке с параметром №22
лишний sensors

[volvo877]

Ты опять нарвалатсь на ту же проблему. Я ж тебе говорил: НЕ ХРАНИ в одной таблице и те данные, которые уже нерелевантны, и те, которые еще рабочие. Сделала по своему - теперь исправляй точно так же, как я уже показывал в прошлой теме.

[SilverFish]

Эти таблицы были созданы до меня.
Вернее я делала таблицы, но после того как все утвердил другой человек.
Сейчас буду исправлять запрос по мотивам прошлой темы.

[volvo877]

И учти, на старых данных (при значении параметра = 12) запрос работать тут же перестанет, так что можешь не торопиться переделывать запрос. Вот к чему приводит неправильное проектирование БД. Переделывай БД.

А это не важно, кто и что создавал до тебя. Тебе не надо переделывать таблицы. Нужно всего навсего добавить новые, для архивации данных, и переносить туда информацию по мере ее устаревания, тогда запросы станут на порядок проще и легче...

[SilverFish]

И учти, на старых данных (при значении параметра = 12) запрос работать тут же перестанет

Этот запрос вроде легче чем предыдущий? Тут не надо агрегирование. Верно?

Переделывай БД.

Мне осталось два запроса. Этот и еще один такой же.

[volvo877]

Ну, тогда вот так попробуй:

Код

SELECT sensors.id, sensors.ser_num, sensors.checkbool
FROM sensors, rooms, rooms_sensors
WHERE 
  rooms_sensors.id_sensors=sensors.id
  AND
  rooms_sensors.id_rooms=rooms.id 
  AND
    (rooms_sensors.id_rooms = 12
 OR
        (0 = (SELECT COUNT(id_sensors) FROM rooms_sensors 
        WHERE id_sensors = sensors.id
                AND 
                 (rooms_sensors.enddata IS NULL)))
 )
  AND
    rooms_sensors.id_rooms IS NOT NULL
  AND 
    rooms_sensors.id_sensors IS NOT NULL
 
 UNION
 
  SELECT sensors.id, sensors.ser_num, sensors.checkbool
  FROM sensors
  LEFT JOIN rooms_sensors 
    ON (rooms_sensors.id_sensors = sensors.id)
  WHERE 
    (rooms_sensors.id_sensors IS NULL OR rooms_sensors.enddata IS NOT NULL)
    AND
    (0 = (SELECT COUNT(id_sensors) FROM rooms_sensors 
            WHERE id_sensors = sensors.id AND rooms_sensors.enddata IS NULL)
     )
 
ORDER BY ser_num;

, тестировать негде, сайт тот перегружен, но причины не работать не вижу...

[SilverFish]

Работает!
Спасибо.