Null values sql join in mysql

Question

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

I don't really understand the problem. Your query seems fine, but you are getting more results from group_group than expected? Please add some sample data. — HoneyBadger
– HoneyBadger, Commented Sep 25, 2017 at 8:54
Probably you have the id=434 present in all the three tables including group hence you are getting groupName for all rows. — Shirish Bari
– Shirish Bari, Commented Sep 25, 2017 at 9:36

Gordon Linoff · Accepted Answer · 2017-09-25 10:33:12Z

1

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.

answered Sep 25, 2017 at 10:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bob Over a year ago

Thanks, it was my structuring mistake. It shows group in all rows since all chats have group attached.

Collectives™ on Stack Overflow

Null values sql join in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related