Как сделать LEFT JOIN в одной таблице?

Question

[freelion93]

Есть таблица Activity, в ней занесена активность действий пользователя.
Добавил файл - added, удалил файл - remove. Собственно нужно получить список id файлов которые были добавлены но не удалены.

Пытаюсь сделать вида:

SELECT object_id FROM activity AS a1 WHERE user = "user1" AND subject = "added" LEFT JOIN activity AS a2 ON a1.object_id != a2.object_id WHERE a2.subject = "remove"

Comments

[longclaps]

нужно получить список id файлов которые были добавлены но не удалены
ON a1.object_id != a2.object_id

Адище.

[freelion93]

longclaps, согласен дичь выходит

Answer 1

[Константин Цветков]

SELECT a1.object_id 
  FROM activity AS a1
    LEFT JOIN activity AS a2 ON a2.object_id = a1.object_id 
  WHERE a1.subject = "added" AND a2.subject = "remove" AND a2.object_id IS NULL

SQL JOIN

Answer 2

[Игорь]

Почему нельзя просто выполнить SELECT с нужным условием, я не совсем понял зачем нужен JOIN.

Ваш запрос абсолютно неверный.
Все присоединения нужно делать в самом начале.
Если вы хотите выполнить JOINпосле WHERE то вам следует обернуть в полноценный подзапрос.

Мне кажется вы слишком усложнили вашу задачу, серьёзно ))

Comments

[freelion93]

Можно конечно сделать и

SELECT object_id FROM activity WHERE user = 'user' AND subject='added' AND object_id NOT IN (SELECT object_id FROM activity WHERE user = 'user' AND subject='removed');

Только суть в том что object id могут быть одинаковые, а их количество отличатся. Скажем пользователь загрузил объект с таким object_id 3 раза и 2 удалил тогда этот файл еще существует.

[Константин Цветков]

freelion93, Если у вас несколько одинаковых записей с одним объектом, то вопрос (и запрос) и его решение не имеет никакого смысла.
Вы нарушили нормальную форму. Для исправления нужно добавить временную метку. Тогда для получения состояния объекта нужно прочитать последнюю запись и не нужно никаких связываний.

[freelion93]

Константин Цветков, подскажите как сделать на основе временной метки, timestamp есть в таблице в UNIX формате. Формат таблицы следующий:

=============================================
activity_id | subject | user | timestamp | object_id
=============================================
1 | added | user | 1584150783 | 11
----------------------------------------------------------------
2 | added | user | 1584143423 | 12
----------------------------------------------------------------
3 | removed | user | 1584523414 | 12
----------------------------------------------------------------
4 | added | user | 1586423417 | 12
----------------------------------------------------------------
5 | added | user | 1588765435 | 13
----------------------------------------------------------------
6 | removed | user | 1584523414 | 11

В итоге мне бы получить те которые которые были остаются. Из таблицы выше получаются object_id 12 и 13

[Константин Цветков]

freelion93, Оформите отдельный вопрос (хотя его задавали уже не один раз). Формат timestamp не важен для получения MAX(timestamp).

[freelion93]

Константин Цветков, получил уже ответ со stackoverflow