CODEWITHSUNDEEP
mysqlsql
Bob
Bob

Reputation: 8714

Null values sql join in mysql

I am doing this query

SELECT m.chat_id, m.msg_sender_id, m.message, m.id,
       DATE_FORMAT(m.created_at, '%Y-%m-%d %H:%i:%s') as created_at,
       g.id as group_id, g.name as group_name, g.image as group_image
FROM message as m 
INNER JOIN chat as c ON c.id = m.chat_id
LEFT OUTER JOIN group as g ON c.group_id = g.id
WHERE c.id = 434
ORDER BY m.id ASC

What I want to achieve is to get all messages from chat and if there is a group involved in chat to return group. Group is attached to chat object (not to message object). I am getting group on all rows for some reason.

Can you advice how can I achieve this?

============= EDIT ==============

group table structure looks like this:

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` longtext COLLATE utf8_bin,
  `status` int(11) DEFAULT NULL,
  `creator_id` int(11) NOT NULL

chat table structure looks like this

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  `person1_id` int(11) NOT NULL,
  `invitation_id` int(11) NOT NULL,

message table looks like this

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL,
  `chat_id` int(11) NOT NULL,
  `msg_sender_id` int(11) NOT NULL,

This is the result from a query

434 14  Some text   900 2017-09-10 09:48:56 3   My group 1  image.jpg
434 13  Some text 2 901 2017-09-10 09:48:59 3   My group 1  image.jpg
434 14  Some text 3 935 2017-09-16 15:37:07 3   My group 1  image.jpg
434 14  Some text 4 968 2017-09-21 06:52:56 3   My group 1  image.jpg

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

This is too long for a comment.

Your query is correct as written. You have an inner join for chat and messages. You have a left outer join for group.

I think you may be misinterpreting the result set. The result set is a table with all columns for each row. You don't get some columns for messages and then other columns for chats. That is how SQL works.

Alternatively, your queries orders by by m.id. Perhaps this ordering happens to bring all the messages with groups to the top.

Upvotes: 1

Related Questions