JohnDel JohnDel - 2 months ago 12
MySQL Question

Mysql the reverse of a group_by statement

I have the following schema:
db schema

The concept goes like this:
We have sellers and buyers. Sellers create tasks and buyers initiate chats and start conversations. So a seller is able to have many chats with different buyers for a task (one chat per buyer and a buyer can initiate just one chat, but he can send many messages) and a chat will have many messages. The message has as an author_id which is either a seller or a buyer.

So I want to find two things (two queries):
One is I want to find all the tasks which doesn't have messages with author_id equals to tasks.seller_id (or don't have messages at all).

Second is, I want to find the reverse, tasks with at least one message with author_id equals to seller_id. The second one is following query ( thanks @bill-karwin ):

SELECT t.id, COUNT(*) AS sellerMessages
FROM tasks AS t
INNER JOIN chats AS c ON c.task_id = t.id
INNER JOIN messages AS m ON m.chat_id = c.id
AND t.seller_id = m.author_id
GROUP BY t.id
HAVING sellerMessages > 0
ORDER BY t.id;


How can I find the reverse?

Update

I've pasted a sql script with records to test: http://pastebin.com/KmMJjQsR
It creates 60 different task records with their messages and chats associations.

Which returns 31 tasks the above sql query and it is correct. I need to find the reverse of the above, which it will return the other 29 tasks.

Creation script

DROP TABLE IF EXISTS `tasks`;

CREATE TABLE `tasks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`seller_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_tasks_on_seller_id` (`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tasks` (`id`, `seller_id`)
VALUES
(15788, 95157),
(15789, 95158),
(15790, 86270),
(15791, 86270),
(15792, 94260),
(15793, 14354),
(15794, 95160),
(15795, 95161),
(15796, 10786),
(15797, 95162),
(15798, 72740),
(15799, 95163),
(15800, 93159),
(15801, 95165),
(15802, 95168),
(15803, 95170),
(15804, 95173),
(15805, 6983),
(15806, 95175),
(15807, 95177),
(15808, 80037),
(15809, 45066),
(15810, 95180),
(15811, 95181),
(15812, 95182),
(15813, 84020),
(15814, 95156),
(15815, 93418),
(15816, 74020),
(15817, 74020),
(15818, 73604),
(15819, 95190),
(15820, 95188),
(15821, 39132),
(15822, 95191),
(15823, 95192),
(15824, 95196),
(15825, 32979),
(15826, 30104),
(15827, 95198),
(15828, 95200),
(15829, 93974),
(15830, 95201),
(15831, 95202),
(15832, 33487),
(15833, 60076),
(15834, 33487),
(15835, 88081),
(15836, 95204),
(15837, 95205),
(15838, 27527),
(15839, 93055),
(15840, 95209),
(15841, 2297),
(15842, 95211),
(15843, 76806),
(15844, 69400),
(15845, 34273),
(15846, 95214),
(15847, 82877);

DROP TABLE IF EXISTS `chats`;

CREATE TABLE `chats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`creator_id` int(11) DEFAULT NULL,
`task_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `chats` (`id`, `creator_id`, `task_id`)
VALUES
(10754, 53058, 15788),
(10767, 36414, 15788),
(10778, 61359, 15788),
(10759, 61359, 15789),
(10770, 36414, 15789),
(10961, 10541, 15790),
(10777, 61359, 15792),
(10819, 11835, 15793),
(10766, 4631, 15795),
(10791, 18461, 15795),
(10823, 31111, 15795),
(10874, 11649, 15795),
(10994, 85902, 15795),
(10769, 36414, 15797),
(10776, 61359, 15797),
(10919, 53058, 15797),
(10771, 12815, 15798),
(10775, 83339, 15798),
(10811, 12745, 15799),
(11211, 35416, 15799),
(10793, 4631, 15800),
(10833, 85902, 15800),
(10816, 36414, 15802),
(10806, 36414, 15803),
(10801, 80049, 15804),
(10818, 36414, 15804),
(10824, 11835, 15805),
(10802, 7240, 15806),
(10822, 39871, 15806),
(10838, 79168, 15806),
(10817, 36414, 15807),
(10918, 53058, 15807),
(10809, 33051, 15808),
(10843, 20435, 15808),
(10803, 45069, 15809),
(10804, 5093, 15809),
(10814, 84699, 15810),
(10844, 58098, 15810),
(10847, 95206, 15810),
(10849, 95105, 15810),
(10853, 94009, 15810),
(10854, 94009, 15810),
(10855, 94009, 15810),
(10856, 94009, 15810),
(10857, 94009, 15810),
(10858, 94009, 15810),
(10859, 94009, 15810),
(10860, 94009, 15810),
(10894, 65435, 15814),
(11057, 91171, 15815),
(10975, 85902, 15816),
(10812, 4631, 15817),
(10841, 85115, 15818),
(10903, 87971, 15818),
(10980, 36414, 15819),
(10886, 39393, 15821),
(10825, 11835, 15824),
(10871, 2919, 15824),
(11194, 21322, 15824),
(11137, 94553, 15825),
(10831, 84932, 15826),
(10836, 83339, 15826),
(10839, 8532, 15826),
(10840, 11967, 15826),
(10913, 35573, 15827),
(10983, 36414, 15827),
(10878, 29425, 15829),
(10952, 36414, 15831),
(10845, 10511, 15832),
(10941, 7240, 15832),
(10872, 3154, 15834),
(10897, 36414, 15835),
(10917, 53058, 15835),
(11045, 36902, 15835),
(10879, 29425, 15836),
(11114, 94682, 15836),
(10900, 36414, 15837),
(10846, 4631, 15838),
(10850, 4631, 15839),
(10848, 4631, 15841),
(10852, 4631, 15842),
(10851, 4631, 15844),
(10864, 62822, 15845),
(10889, 85115, 15847);

DROP TABLE IF EXISTS `messages`;

CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`chat_id` int(11) DEFAULT NULL,
`author_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `messages` (`chat_id`, `author_id`)
values
(10754, 53058),
(10767, 36414),
(10778, 61359),
(10759, 61359),
(10759, 95158),
(10770, 36414),
(10770, 95158),
(10961, 10541),
(10961, 86270),
(10777, 61359),
(10819, 11835),
(10819, 14354),
(10766, 4631),
(10766, 95161),
(10791, 95161),
(10791, 18461),
(10823, 31111),
(10874, 11649),
(10994, 85902),
(10769, 36414),
(10776, 61359),
(10919, 53058),
(10775, 83339),
(10775, 72740),
(10811, 12745),
(10811, 95163),
(11211, 35416),
(11211, 95163),
(10793, 4631),
(10793, 93159),
(10833, 85902),
(10833, 93159),
(10816, 36414),
(10806, 36414),
(10801, 80049),
(10818, 36414),
(10824, 11835),
(10824, 6983),
(10802, 7240),
(10802, 95175),
(10822, 39871),
(10838, 79168),
(10838, 95175),
(10817, 36414),
(10817, 95177),
(10918, 53058),
(10809, 33051),
(10809, 80037),
(10843, 20435),
(10843, 80037),
(10803, 45069),
(10804, 5093),
(10814, 84699),
(10814, 95180),
(10844, 58098),
(10844, 95180),
(10847, 95206),
(10847, 95180),
(10849, 95105),
(10849, 95180),
(10853, 94009),
(10853, 95180),
(10894, 65435),
(10894, 95156),
(11057, 91171),
(10975, 85902),
(10975, 74020),
(10812, 4631),
(10812, 74020),
(10841, 85115),
(10841, 73604),
(10903, 87971),
(10903, 73604),
(10980, 36414),
(10980, 95190),
(10886, 39393),
(10886, 39132),
(10825, 11835),
(10825, 95196),
(10871, 2919),
(10871, 95196),
(11194, 21322),
(11194, 95196),
(11137, 94553),
(10831, 84932),
(10836, 83339),
(10839, 8532),
(10839, 30104),
(10840, 11967),
(10913, 35573),
(10913, 95198),
(10983, 36414),
(10878, 29425),
(10878, 93974),
(10952, 36414),
(10845, 33487),
(10845, 10511),
(10941, 7240),
(10872, 3154),
(10872, 33487),
(10897, 36414),
(10897, 88081),
(10917, 53058),
(11045, 36902),
(11045, 88081),
(10879, 29425),
(10879, 95204),
(11114, 94682),
(10900, 36414),
(10900, 95205),
(10846, 4631),
(10846, 27527),
(10850, 4631),
(10850, 93055),
(10848, 4631),
(10852, 4631),
(10852, 95211),
(10851, 4631),
(10851, 69400),
(10864, 62822),
(10889, 85115);


Results:

In one query (the one with the included
sender_id
on messages) there should be returned either
31
or the job ids

15789,15790,15793,15795,15798,15799,15800,
15805,15806,15807,‌​15808,15810,15814,15‌​816,
15817,15818,1581‌​9,15821,15824,15826,‌​15827,
15829,15832,15‌​834,15835,15836,1583‌​7,15838,
15839,15842,‌​15844


and in the other query the result should be

15841,15796,15825,15845,15809,15833,15843,
15847,15813,15791,‌​15815,15792,15788,15‌​794,
15797,15801,1580‌​2,15803,15804,15811,‌​15812,
15820,15822,15‌​823,15828,15830,1583‌​1,15840,15846


or
29
.

Answer

I want to find ... tasks with at least one message with author_id equals to seller_id.

Your query in the question can be simplified a bit. There is no need for explicit HAVING filter, because INNER JOINs would do it. If you don't need count of messages, you can omit the COUNT.

SELECT t.id
FROM tasks AS t
INNER JOIN chats AS c ON c.task_id = t.id
INNER JOIN messages AS m 
    ON  m.chat_id = c.id
    AND t.seller_id = m.author_id
GROUP BY t.id
ORDER BY t.id;

I want to find all the tasks which don't have messages with author_id equal to tasks.seller_id (or don't have messages at all).

One very straight-forward way to get the reverse of the top query is to use it as a sub-query for NOT IN:

SELECT tasks.id
FROM tasks
WHERE
  tasks.id NOT IN
  (
    SELECT t.id
    FROM tasks AS t
    INNER JOIN chats AS c ON c.task_id = t.id
    INNER JOIN messages AS m 
        ON  m.chat_id = c.id
        AND t.seller_id = m.author_id
  )
;

Here is SQL Fiddle.

Comments