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.
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.