MySQL - Query not returning proper results

Question

CREATE TABLE `swipes` (
  `swp_id` bigint(20) NOT NULL,
  `swp_by` bigint(20) NOT NULL,
  `swp_to` bigint(20) NOT NULL,
  `swp_type` varchar(255) NOT NULL,
  `swp_status` enum('requested','accepted','declined') NOT NULL,
  `swp_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `swipes` (`swp_id`, `swp_by`, `swp_to`, `swp_type`, `swp_status`, `swp_date`) VALUES
(1, 8, 11, 'top', 'accepted', '2020-04-18 20:48:45'),
(2, 1, 11, 'right', 'accepted', '2020-04-18 20:41:49'),
(3, 12, 1, 'right', 'accepted', '2020-04-18 20:41:49'),
(4, 13, 1, 'right', 'accepted', '2020-04-18 20:41:49'),
(5, 1, 14, 'right', 'accepted', '2020-04-18 20:41:49'),
(6, 1, 15, 'top', 'accepted', '2020-04-18 20:41:49');


CREATE TABLE `messages` (
  `msg_id` bigint(20) NOT NULL,
  `msg_from` bigint(20) NOT NULL,
  `msg_to` bigint(20) NOT NULL,
  `msg_message` longtext NOT NULL,
  `msg_seen` enum('yes','no') NOT NULL DEFAULT 'no',
  `msg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `messages` (`msg_id`, `msg_from`, `msg_to`, `msg_message`, `msg_seen`, `msg_time`) VALUES
(1, 11, 1, 'How are you?', 'yes', '2020-04-14 21:01:05'),
(3, 1, 11, 'I am fine.. you?', 'no', '2020-04-14 20:54:07'),
(4, 1, 8, 'How are you?', 'yes', '2020-04-14 21:01:05'),
(5, 8, 1, 'I am good... You say?', 'yes', '2020-04-14 21:13:34'),
(6, 1, 11, 'Thik hun... Tum batao..', 'yes', '2020-04-14 21:16:05'),
(7, 11, 1, 'Okay', 'yes', '2020-04-16 09:16:39'),
(8, 8, 1, 'Yes, it\'s a good idea.', 'yes', '2020-04-16 09:16:39'),
(9, 1, 8, 'Thought so.. Would you like to join?', 'yes', '2020-04-16 09:23:39'),
(10, 8, 1, 'Are you there?', 'yes', '2020-04-23 11:57:39'),
(12, 8, 1, 'Would you like to join?', 'yes', '2020-04-23 10:42:27'),
(13, 1, 11, 'We will arrange things for you :)', 'yes', '2020-04-23 10:59:04');

Fiddle: DB FIDDLE

In the fiddle above my query is returning correct data but it seems to eliminate results when 1 is present in multiple records of swp_by in swipes table. At first I thought it was because of GROUP BY swipes.swp_by but I removed it and it seemed like it wasn't the issue so I placed it back. When you run the query you see that currently the query returns result for swp_id 2, 3 & 4 but not for 5 & 6. They are eliminated and it's because in swp_by 1 occurred in swp_id 2 once. I want the query to return the results for 5 & 6 as well.

Query

SELECT 
    swp_id,
    swp_by,
    swp_to,
    msg_from,
    msg_to,
    msg_message,
    GREATEST(MAX(msg_time), swipes.swp_date) AS msgdate,
    COUNT(msg_id) AS msgcnt
FROM
    swipes
        LEFT JOIN
    (SELECT 
        *
    FROM
        messages
    ORDER BY msg_time DESC) messages ON ((messages.msg_from = swipes.swp_by
        AND messages.msg_to = swipes.swp_to)
        OR (messages.msg_from = swipes.swp_to
        AND messages.msg_to = swipes.swp_by))
WHERE
    (swipes.swp_by = 1 OR swipes.swp_to = 1)
        AND swipes.swp_status = 'accepted'
GROUP BY swipes.swp_by
ORDER BY GREATEST(MAX(messages.msg_time),
        MAX(swipes.swp_date)) DESC

What is this all about?

I am setting up a chatting system and users who are matched are able chat with each other. swipes stores the matches and messages are the transacted messages between the users. With this query I am trying to set up the home list of matched users to chat with where you tap/click a user and the chat box appears (will make that later). I thought just giving a brief about the project might help in understanding the problem.

The message between 1 and eleven are reduced to one by the group by and also themessages to 14 and 15 you can see here db-fiddle.com/f/2yKt6d5RWngXVYJKPGZL6m/0 where i extended the group by, but i don't understand what you are looking for a result table would help imensly — nbk
– nbk, Commented Apr 26, 2020 at 19:02
@nbk it's not because of GROUP BY.. I think you did not read my full description (above the query). I mentioned that I tried to remove the group by but it of no avail. Please check it once. — Code With Desire
– Code With Desire, Commented Apr 26, 2020 at 19:47

nbk · Accepted Answer · 2020-04-26 19:57:57Z

1

I post my asnwer here the fiddle doesn't give the right link

no got it https://www.db-fiddle.com/f/2yKt6d5RWngXVYJKPGZL6m/2

SELECT 
    swp_id,
    swp_by,
    swp_to,
    msg_from,
    msg_to,
    msg_message ,
    GREATEST(MAX(msg_time), swipes.swp_date) AS msgdate,
    COUNT(msg_id) AS msgcnt
FROM
    swipes
        LEFT JOIN
    (SELECT 
        *
    FROM
        messages
    ORDER BY msg_time DESC) messages 
    ON (messages.msg_from = swipes.swp_by
        AND messages.msg_to = swipes.swp_to)
        OR 
        (messages.msg_from = swipes.swp_to
        AND messages.msg_to = swipes.swp_by)
WHERE
    (swipes.swp_by = 1 OR swipes.swp_to = 1)
        AND swipes.swp_status = 'accepted'
GROUP BY swipes.swp_by,swipes.swp_to
ORDER BY GREATEST(MAX(messages.msg_time),
        MAX(swipes.swp_date)) DESC

answered Apr 26, 2020 at 19:57

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

3 Comments

Code With Desire Over a year ago

The fiddle will give the right link if you click on UPDATE on the navigation bar above once you have finished

nbk Over a year ago

i have fugured it out so the link now is the correct one,

Code With Desire Over a year ago

Okay... So all that was needed was adding swipes.swp_to in my GROUP BY... Man, this much you could have said in the comments as well.. Anyways... Thanks a lot.. that solved my issue.. I am grateful to you that you took your time for me :) Love bro <3 :)

Collectives™ on Stack Overflow

MySQL - Query not returning proper results

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related