vendredi 1 avril 2016

How to make a join, where last table is limited to 1 or 0?

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

enter image description here

Aucun commentaire:

Enregistrer un commentaire