CODEWITHSUNDEEP
sqlsqlite
Yarh
Yarh

Reputation: 4607

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

Upvotes: 0

Views: 39

Answers (2)

Yarh
Yarh

Reputation: 4607

I end up with 

CREATE VIEW chat_view AS  SELECT chat_table._id AS _id, chat_table.photo AS photo, chat_table.interlocutor_id AS interlocutor_id, chat_table.chat_id AS view_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,  MAX ( message_table.time )  AS time  FROM chat_table LEFT JOIN message_table ON chat_table.chat_id = message_table.chat_id GROUP BY chat_table.chat_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You would use LEFT JOIN, but you need to do it twice:

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

Note that most databases support the ANSI standard ROW_NUMBER() function, which provides a simpler method for getting the most recent message.

Upvotes: 1

Related Questions