Subquery result returning comma separated values using IN clause MySQL

Question

Friends, two tables one table is

CREATE TABLE `vbw_push_notifications` (
  `push_notification_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ,Auto increment field',
  `push_notification_customer_ids` text NOT NULL COMMENT 'comma separated customer id list which was used for messaging/related customers/broadcasting',
  `push_notification_message` varchar(500) NOT NULL COMMENT 'The notification message.(A new message from Veebow/A new message from <Merchant Name>/A new public deal <Deal Name> from <Merchant Name>/A new game deal <Deal Name> from <Merchant Name>',
  `push_notification_time` datetime NOT NULL,
  `push_notification_is_processed` tinyint(4) NOT NULL,
  PRIMARY KEY (`push_notification_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='The comma separated customer ids of the customers who needs ';

-- ----------------------------
-- Records of vbw_push_notifications
-- ----------------------------
INSERT INTO `vbw_push_notifications` VALUES ('1', '165836,65802,65829,65837,65838', 'test test test', '2013-11-07 12:36:42', '0');

And I have another table with the following details.

  CREATE TABLE `vbw_mobile_sessions` (
  `mobile_session_id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'The unique identifier for a mobile session',
  `mobile_session_start_time` datetime DEFAULT NULL COMMENT 'The starting time @ server of a mobile session',
  `mobile_session_end_time` datetime DEFAULT NULL COMMENT 'The ending time @ server of a mobile session',
  `mobile_session_token` varchar(255) DEFAULT NULL COMMENT 'The mobile session token generated for this session',
  `mobile_session_device_id` varchar(50) DEFAULT NULL COMMENT 'The device id of the device used for making the session',
  `mobile_session_customer_id` int(10) DEFAULT NULL COMMENT 'The customer ID of the customer who made this session',
  `mobile_session_device_type` tinyint(4) DEFAULT NULL COMMENT 'The type of device that customer uses for this session. 0 - iOS, 1 - Android',
  PRIMARY KEY (`mobile_session_id`),
  KEY `fk_mobile_session_customer_id` (`mobile_session_customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=677 DEFAULT CHARSET=latin1 COMMENT='This table holds the merchant account activation links creat';

I want to use a subquery like this.

SELECT DISTINCT(mobile_session_customer_id)
FROM vbw_mobile_sessions
WHERE mobile_session_end_time IS null
AND mobile_session_customer_id IN (SELECT push_notification_customer_ids FROM vbw_push_notifications WHERE push_notification_id=6) .

This query not returns zero rows. But i am getting result when i have used like this.

    SELECT DISTINCT(mobile_session_customer_id)
FROM vbw_mobile_sessions
WHERE mobile_session_end_time IS null
AND mobile_session_customer_id IN ( SELECT DISTINCT(mobile_session_customer_id)
FROM vbw_mobile_sessions
WHERE mobile_session_end_time IS null
AND mobile_session_customer_id IN (65836,65802,65829,65837,65838)

I think its in a different format the subquery is returning the result . Can you please point out the mistake i have made. Many Thanks.

You would maybe see better the difference with strings : there's a big difference between IN('A', 'B', 'C') and IN('A, B, C') — Raphaël Althaus
– Raphaël Althaus, Commented Nov 7, 2013 at 7:24

Alma Do · Accepted Answer · 2013-11-07 07:26:13Z

1

Your answer is obvious. String 'a,b,c,d' has nothing to do with set of values (a,b,c,d). This is not how it will work.

The correct solution is not to use delimiter-separated values in one field. You should normalize your DB structure and create linking table. Then place your values into it and build your query with using subquery, selecting from it.

Another, possible solution is to select your data (string data) from your field in application, split it by delimiter and substitute to another query then.

answered Nov 7, 2013 at 7:26

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:17:14Z

0

The subquery is returning you a varchar with a value '1,2,3' and you need a set of integers which is 1,2,3...

The engine is treating your result of the subquery as a varchar() not a set of integers.

You can go through this question, asking exactly what you need

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Nov 7, 2013 at 7:25

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

Subquery result returning comma separated values using IN clause MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related