Как составить SQL запрос для подставления значения по времени?

Question

[Мария]

Есть 2 таблицы

Сhange log :
id| old_state| new_state |date
1 | 34| 98| 2024-05-10
1 | 98| 65| 2024-05-13

id - айди переписки
old_state - старый статус
new_state - статус на который поменялся
date - когда статус поменялся

И
List :
id| date_massage| text
1 | 2024-05-11| Привет! Как дела?
1 | 2024-05-15 | Хеллоу! Все оки
История сообщений в переписке

Статусы меняются независимо от сообщений в переписке.

Задача видеть в каком статусе пришло то или иное сообщение, т.е. получить по итогу такое:
id| date_massage| text | state
1 | 2024-05-11| Привет! Как дела? | 98
1 | 2024-05-15 | Хеллоу! Все оки | 65

Comments

[Rsa97]

Какая СУБД? Диалекты SQL разные бывают.

[Vitsliputsli]

Задача видеть в каком статусе пришло то или иное сообщение

Значит вам нужна связь между статусами и сообщениями, в описанной схеме ее пока нет.

Answer 1

[ThunderCat]

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

Вывод: идите учите 1, 2 и 3 нормальные формы для начала. Потом нормально создайте таблицы. Потом скорее всего все вопросы сами отпадут, но если нет - вернетесь с нормальным вопросом.

Comments

[Мария]

Эти таблицы создавала не я. Работаю уже с тем что имеется, и к сожалению имею такие таблицы.

Могу добавить к каждой строке уникальный идентификатор, с его добавлением задача будет решаемой?

[ThunderCat]

Мария,

Могу добавить к каждой строке уникальный идентификатор, с его добавлением задача будет решаемой?

Во первых - добавить допустим можете, а связывать уже существующие записи между собой как будете?

Во вторых, нужно соответствующее поле для связки сообщения со статусом, то есть в таблице статусов у вас должно быть message_id, указывающее на уникальный id сообщения.

В третьих, я все не могу понять причем тут массаж (date_massage)...

Answer 2

[DKSoft]

В вопросе не указана БД (((
Для MySQL

select l.*
      ,(select new_status 
          from change_log ch
            where ch.id=l.id 
               and ch.date<=l.date_message 
                 order by ch.date desc 
                   limit 1) as state
 from List l

В общем случае

select l.*
      ,(select new_status
         from change_log ch 
          where ch.id=l.id 
              and ch.date=(select max(ch.date) 
                                    from change_log ch
                                      where ch.date<=l.date_message)
     ) as state
 from List l