I have 2 tables: chat_table and message_table.
I want to make view, which cointains row from chat table + message and time from message_table if present.
CREATE VIEW chat_view AS
SELECT
chat_table._id AS _id,
chat_table.chat_id AS chat_id,
chat_table.title AS title,
chat_table.title_lower AS title_lower,
chat_table.interlocutor_username AS interlocutor_username,
chat_table.interlocutor_lastname AS interlocutor_lastname,
chat_table.interlocutor_name AS interlocutor_name,
chat_table.interlocutor_username_lower AS interlocutor_username_lower,
chat_table.interlocutor_lastname_lower AS interlocutor_lastname_lower,
chat_table.interlocutor_photo AS interlocutor_photo,
chat_table.status AS status,
chat_table.type AS type,
chat_table.client_id AS client_id,
message_table.message AS message,
message_table.message_lower AS message_lower,
message_table.time AS time
FROM
chat_table
INNER JOIN
message_table
ON
chat_table.chat_id = message_table.chat_id
INNER JOIN
( SELECT chat_id,
MAX( time ) AS time FROM message_table GROUP BY chat_id ) b
ON
b.chat_id = message_table.chat_id AND message_table.time = b.time
Currently this statment creates a view, which displays chat with their last message, ignoring chats without messages.
Hot to include chates without messages?
EDIT
About left join
CREATE VIEW chat_view5 AS SELECT chat_table._id AS _id, chat_table.chat_id AS chat_id, chat_table.title AS title, chat_table.title_lower AS title_lower, chat_table.interlocutor_username AS interlocutor_username, chat_table.interlocutor_lastname AS interlocutor_lastname, chat_table.interlocutor_name AS interlocutor_name, chat_table.interlocutor_username_lower AS interlocutor_username_lower, chat_table.interlocutor_lastname_lower AS interlocutor_lastname_lower, chat_table.interlocutor_photo AS interlocutor_photo, chat_table.status AS status, chat_table.type AS type, chat_table.client_id AS client_id, message_table.message AS message, message_table.message_lower AS message_lower, message_table.time AS time FROM chat_table LEFT JOIN message_table ON chat_table.chat_id = message_table.chat_id LEFT JOIN ( SELECT chat_id, MAX( time ) AS time FROM message_table GROUP BY chat_id ) b ON b.chat_id = message_table.chat_id AND message_table.time = b.time
Aucun commentaire:
Enregistrer un commentaire