Mysql complex select statement

Question

I have a case which seems very complex to me and I am currently doing it in 3 queries and with lots of application logic. I thought, it may be possible with only one single queries.

I have a three table,

1. conversations table 
2. chats table
3. users table 

Please see the two pictures so that you can see the entities with the actual data.

conversations table

chats table

Please look at the conversation table. Here I want to get all the rows where sender=1 or reciever=1 so expected results will be id 3,4,5. Now, I also return the user details from these selected rows.

Here is the most difficult part for me. I don't want to retrieve users details of id 1 instead I want users details whose id are 2,3 and 4 notice 4 in sender column and 2 3 in reciever column

Now, you know the selected rows from conversations table. In second table, con_id is the foreign key of conversation table so I want to retrieve the last rows of each con_id. In the second picture, you can see id 2,3,4,5,6 has con_id = 3 but since I want the last one so it should select where id = 6 similarly all last row of each con_id

I am sorry for this long case, hope you got me and the problem.

Thanks in advance.

EDIT Here is the sql tables and dummy data for you so that if you want you can test paste quickly

Expected results

   id || sender || reciver || id(users id) ||  userName   || id(chats id) || con_id || msg || msg_sender
   3    1          2           2             iamsadek2          6              3      ...      2
   4    1          3           3             sadek3             10             4      ...      3
   5    4          1           4             adek4.             14.            5.     ...      4 








DROP TABLE IF EXISTS `chats`;

CREATE TABLE `chats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `msg_sender` int(10) unsigned DEFAULT NULL,
  `con_id` int(10) unsigned DEFAULT NULL,
  `msg` text,
  `file` varchar(255) DEFAULT NULL,
  `deleted` int(10) unsigned DEFAULT NULL,
  `seen` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `con_id` (`con_id`),
  KEY `msg_sender` (`msg_sender`),
  CONSTRAINT `chats_ibfk_1` FOREIGN KEY (`con_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chats_ibfk_2` FOREIGN KEY (`msg_sender`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `chats` WRITE;
/*!40000 ALTER TABLE `chats` DISABLE KEYS */;

INSERT INTO `chats` (`id`, `msg_sender`, `con_id`, `msg`, `file`, `deleted`, `seen`, `created_at`, `updated_at`)
VALUES
    (2,1,3,'id 1 sending msg to id 2',NULL,NULL,0,'2018-06-26 19:36:06',NULL),
    (4,2,3,'id 2 sending msg to id 1 second msg',NULL,NULL,0,'2018-06-26 19:36:37',NULL),
    (5,1,3,'id 1 sending msg to id 2 msg 3',NULL,NULL,0,'2018-06-26 19:36:42',NULL),
    (6,2,3,'id 2 sending msg to id 1 msg 4',NULL,NULL,0,'2018-06-26 19:36:46',NULL),
    (7,1,4,'id 1 sending msg to id 3 msg 1',NULL,NULL,0,'2018-06-26 19:36:49',NULL),
    (8,3,4,'id 3 sending msg to id 1 msg 2am',NULL,NULL,0,'2018-06-26 19:39:44',NULL),
    (9,3,4,'id 3 sending msg to id 1 msg 3',NULL,NULL,0,'2018-06-26 19:39:55',NULL),
    (10,3,4,'id 3 sending msg to id 1 msg 4',NULL,NULL,0,'2018-06-26 19:39:57',NULL),
    (11,4,5,'id 4 sending msg to id 1 msg 1',NULL,NULL,0,'2018-06-26 19:40:46',NULL),
    (12,4,5,'id 4 sending msg to id 1 msg 1',NULL,NULL,0,'2018-06-26 19:40:48',NULL),
    (13,4,5,'id 4 sending msg to id 1 msg 3',NULL,NULL,0,'2018-06-26 19:40:50',NULL),
    (14,1,5,'id 1 sending msg to id 4 msg 4',NULL,NULL,0,'2018-06-26 19:41:01',NULL),
    (15,4,11,'id 4 sending msg to id 3 msg 1',NULL,NULL,0,'2018-06-26 19:42:37',NULL),
    (16,3,11,'id 3 sending msg to id 4 msg 2',NULL,NULL,0,'2018-06-26 19:42:57',NULL),
    (17,3,11,'id 3 sending msg to id 4 msg 2',NULL,NULL,0,'2018-06-26 19:42:59',NULL);

/*!40000 ALTER TABLE `chats` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table conversations
# ------------------------------------------------------------

DROP TABLE IF EXISTS `conversations`;

CREATE TABLE `conversations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sender` int(10) unsigned DEFAULT NULL,
  `reciever` int(10) unsigned DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `type` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sender` (`sender`),
  KEY `reciever` (`reciever`),
  CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`sender`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`reciever`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conversations_reciever_foreign` FOREIGN KEY (`reciever`) REFERENCES `users` (`id`),
  CONSTRAINT `conversations_sender_foreign` FOREIGN KEY (`sender`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `conversations` WRITE;
/*!40000 ALTER TABLE `conversations` DISABLE KEYS */;

INSERT INTO `conversations` (`id`, `sender`, `reciever`, `status`, `type`, `created_at`, `updated_at`)
VALUES
    (3,1,2,0,0,'2018-06-26 19:32:35',NULL),
    (4,1,3,0,0,'2018-06-26 19:32:50',NULL),
    (5,4,1,0,0,'2018-06-26 19:33:11',NULL),
    (6,2,3,0,0,'2018-06-26 19:33:22',NULL),
    (11,3,4,0,0,'2018-06-26 19:33:22',NULL);

/*!40000 ALTER TABLE `conversations` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table users
# ------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL,
  `profilePic` varchar(255) DEFAULT 'user.png',
  `address` varchar(255) DEFAULT NULL,
  `lat` varchar(255) DEFAULT NULL,
  `lang` varchar(255) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `userName` varchar(255) NOT NULL,
  `gender` varchar(11) DEFAULT NULL,
  `userType` varchar(255) DEFAULT 'User',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `status` varchar(11) DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_username_unique` (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;

INSERT INTO `users` (`id`, `firstName`, `lastName`, `profilePic`, `address`, `lat`, `lang`, `ip`, `password`, `email`, `userName`, `gender`, `userType`, `created_at`, `updated_at`, `status`)
VALUES
    (1,'sadek','hossain','user.png',NULL,NULL,NULL,NULL,'$2a$10$ayAJE7BxkgqXPGjVxeD8iu0GVWL6CXJFdLhGcHAN.i39lCqcAR5nS','[email protected]','iamsadek','male','User','2018-06-26 18:33:16','2018-06-26 18:33:16','active'),
    (2,'sadek2','hossain2','user.png',NULL,NULL,NULL,NULL,'$2a$10$q.LNN48POO9g1INdEC/iTO1CJjGXNBLYZPbHkyRe.oHaZJi9b8GWe','[email protected]','iamsadek2','male','User','2018-06-26 18:42:53','2018-06-26 18:42:53','active'),
    (3,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek3','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
    (4,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek4','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
    (5,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek5','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
    (6,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek6','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active');

/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

Answer 1

It can basically be done with some joins. The tricky part is the last message for chats. The subquery groups the chats by the con_id to get the maximum id. As id is AUTO_INCREMENT, the maximum id for a con_id is the last message of the conversation.

SELECT co1.id,
       co1.sender,
       co1.reciever,
       us.id "id(users id)",
       us.username,
       ch1.id "id(chats id)",
       ch1.con_id,
       ch1.msg,
       ch1.msg_sender
       FROM conversations co1
            INNER JOIN users us
                       ON CASE
                            WHEN co1.sender <> 1
                              THEN co1.sender
                            WHEN co1.reciever <> 1
                              THEN co1.reciever
                          END = us.id
            INNER JOIN chats ch1
                       ON ch1.con_id = co1.id
            INNER JOIN (SELECT max(ch2.id) id,
                               ch2.con_id
                               FROM chats ch2
                               GROUP BY ch2.con_id) ch3
                       ON ch3.con_id = ch1.con_id
                          AND ch3.id = ch1.id
       WHERE 1 IN (co1.sender,
                   co1.reciever);

SQL Fiddle

Instead of an INNER JOIN with the subquery with the GROUP BY, a correlated subyquery would be another option.

SELECT co1.id,
       co1.sender,
       co1.reciever,
       us.id "id(users id)",
       us.username,
       ch1.id "id(chats id)",
       ch1.con_id,
       ch1.msg,
       ch1.msg_sender
       FROM conversations co1
            INNER JOIN users us
                       ON CASE
                            WHEN co1.sender <> 1
                              THEN co1.sender
                            WHEN co1.reciever <> 1
                              THEN co1.reciever
                          END = us.id
            INNER JOIN chats ch1
                       ON ch1.con_id = co1.id
       WHERE 1 IN (co1.sender,
                   co1.reciever)
             AND ch1.id = (SELECT max(ch2.id)
                                  FROM chats ch2
                                  WHERE ch2.con_id = co1.id);

SQL Fiddle

Answer 2

TRy it now

      SELECT id.conversations, sender.conversations, receiver.conversations, id.users, userName.users, id.chats, con_id.chats, msg.chats, msg_sender.chats
  FROM conversations, chats, users
 WHERE (sender.conversations OR receiver.conversations = 1) AND id.conversations=firstname.users 
    GROUP BY id.conversations