CODEWITHSUNDEEP
phpmysqlsql
pandu das
pandu das

Reputation: 230

How to get unread message from mysql

Messages are broadcast to users. So creating only one record of message in updates table.

table name : updates
id : 
title : message
user_id : created user id

table name : message _status
id
message_id
user_id : read by user

If any user read message we make entry in updates_status table.

I want to show list of unread message those are not created by current user and unread. Here is my query:

select u.*, s.user_id as status_user, s.id 
 from updates as u left outer join update_status as s ON u.id=s.update_id 
 where u.user_id != 1 and (s.user_id is null or s.user_id  != 1)

I am getting 6 records instead of 5.

Here is the DDL from the fiddle:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `u_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `u_name`) VALUES
(1, 'Praveen'),
(2, 'Ravi'),
(3, 'Ram'),
(4, 'Mohan');

CREATE TABLE IF NOT EXISTS `updates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `updates`
--

INSERT INTO `updates` (`id`, `title`, `user_id`, `created`) VALUES
(1, 'message1', 2, '2015-09-28 15:50:38'),
(2, 'message2', 2, '2015-09-28 15:50:38'),
(3, 'message3', 1, '2015-09-28 17:00:37'),
(4, 'message4', 1, '2015-09-28 17:00:37'),
(5, 'message5', 3, '2015-09-28 17:05:21'),
(6, 'message6', 4, '2015-09-28 17:05:21');

CREATE TABLE IF NOT EXISTS `update_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `update_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `update_status`
--

INSERT INTO `update_status` (`id`, `update_id`, `user_id`, `created`) VALUES
(2, 1, 1, '2015-09-28 17:02:15'),
(3, 3, 2, '2015-09-28 17:04:16'),
(4, 1, 3, '2015-09-28 17:06:24'),
(5, 1, 4, '2015-09-28 17:06:33'),
(6, 3, 3, '2015-09-28 17:17:12');

Upvotes: 2

Views: 573

Answers (1)

user2762134
user2762134

Reputation:

Changed to exclude users own updates

SELECT Updates.id 
FROM Updates 
JOIN Users 
ON Updates.user_id != Users.id and Users.id = 1 -- CURRENT USER ID
LEFT JOIN Update_status 
ON Users.id = update_status.user_id 
and updates.id = update_status.id 
WHERE update_status.created is null 
ORDER BY updates.created desc

returns

id
--
5
6
1

Upvotes: 3

Related Questions