я делаю удаление личных сообщений
суть такая ... как я это вижу ... надо чтоб когда человек удаляет сообщение оно не отображалось только ему ... у второго человека этого диалога оно должно отображаться ...
поэтому я решил что сделаю не удаление а скрытие этого сообщения для этого человека
создал таблицу chat_priv_message_dell там столбцы id, iduser, idpmmess
по идеи как я представляю когда человек нажмет на сообщении удалить то в таблицу chat_priv_message_dell запишется запись с его идом и идом сообщения ...
потом при выборке личных сообщений будем проверять есть ли в таблице chat_priv_message_dell запись с идом этого пользователя AND идом сообщения ...
и если есть то не выводить это сообщение. ..
если сделать через два запроса я представляю как это будет ...
но решил сделать одним запросов для оптимизации ... но чего то этот двойной запрос не идет у меня ... я с ними не очень работаю ....

вот какой набросок у меня получился ... но он дает ошибку в синтаксисе ... помогите понять что где дописать ...

SELECT id as tid, (SELECT COUNT(*) FROM chat_priv_message_dell WHERE idpmmess=tid AND idusers=2) as counts FROM chat_priv_message where (id_to=1 AND id_from=2)or(id_to=2 AND id_from=1) ORDER BY date DESC LIMIT 0,100

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

SELECT id as tid, (SELECT COUNT(*) FROM chat_priv_message_dell WHERE idpmmess=tid AND idusers=2) as counts FROM chat_priv_message where ((id_to=1 AND id_from=2)or(id_to=2 AND id_from=1)) AND counts=0 ORDER BY date DESC LIMIT 0,100

#1054 - Unknown column 'counts' in 'where clause'

да я тоже сначала так думал ... но ... я рассчитываю на будущее что у меня будут мультидиалоги ... то есть не на 2 человека а на более ... поэтому сразу решил делать через дополнительную таблицу ...

сейчас методом экспериментов пришел к такому запросу

SELECT * FROM chat_priv_message as t LEFT JOIN chat_priv_message_dell as td ON
(td.idpmmess=t.id AND td.idusers=2)
WHERE
td.id IS NULL and
((t.id_to=534 AND t.id_from=2)or(t.id_to=2 AND t.id_from=534))
ORDER BY t.date DESC LIMIT 0,100

работает как надо ... только вопрос насколько этот вариант оптимизирован и можно ли лучше ?

ваш вариант выходит вот так:
SELECT id as tid FROM chat_priv_message where ((id_to=1 AND id_from=2)or(id_to=2 AND id_from=1)) AND 0 = (SELECT COUNT(*) FROM chat_priv_message_dell WHERE idpmmess=tid AND idusers=2) ORDER BY date DESC LIMIT 0,100

#1054 - Unknown column 'tid' in 'where clause'

пока что систему обтачиваю как камень ... и выглядит очень страшно ... и на текущем этапе не получится минусовать иды ... потому что но ним идет выборка диалогов данного пользователя ...

SELECT max(date), id_from, id_to FROM chat_priv_message where id_to=$idusers OR id_from=$idusers GROUP BY id_to+id_from ORDER BY max(date) DESC LIMIT 0,50

в будущем этих столбцов вообще не будет ...
думаю сделаю столбец id_dialog
будет таблица dialog (id, name)
и будет таблица user_in_dialog (id, iddialog, iduser)

ну а конкретно по текущей реализации запроса

SELECT t.id, id_to, id_from, date, mess, is_read FROM chat_priv_message as t
LEFT JOIN chat_priv_message_dell as td
ON (td.idpmmess=t.id AND td.idusers=871)
WHERE
td.id IS NULL
AND
((t.id_to=871 AND t.id_from=924)or(t.id_to=924 AND t.id_from=871))
ORDER BY t.date DESC LIMIT 0,100

это нормально или можно это же но быстрее сделать ?

а я понял что вы имели ввиду ... я сначала подумал вы имели ввиду просто -1 ставить ...
ну идея хорошая .. .но опять же все обтачиваю и подвожу к новой системе мультидиалогов ... поэтому вопрос остается тот же ...
caHek2xну а конкретно по текущей реализации запроса

SELECT t.id, id_to, id_from, date, mess, is_read FROM chat_priv_message as t
LEFT JOIN chat_priv_message_dell as td
ON (td.idpmmess=t.id AND td.idusers=871)
WHERE
td.id IS NULL
AND
((t.id_to=871 AND t.id_from=924)or(t.id_to=924 AND t.id_from=871))
ORDER BY t.date DESC LIMIT 0,100

это нормально или можно это же но быстрее сделать ?

при считывании списка диалогов получилось надо же учесть то что человек мог удалить(скрыть) все сообщения в диалоге .. .и тогда не выводить в список диалог с этим пользователем ...
что то сделал ... но запрос долгий ... может как можно проще его переписать ?