MYSQL returns empty result set?

Question

I have a table with the following structure:

CREATE TABLE IF NOT EXISTS `user_settings` (
  `user_ID` smallint(6) unsigned NOT NULL,
  `item` varchar(50) NOT NULL,
  `value` text,
  `app_ID` tinyint(4) NOT NULL DEFAULT '0',
  KEY `user_ID` (`user_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# With these data

INSERT INTO `user_settings` (`user_ID`, `item`, `value`, `app_ID`) VALUES
(3, 'statusTrainer_ID', '0', 0),
(3, 'enabledApp', '1', 0),
(3, 'enabled_office_ID', '1', 1),
(3, 'enabled_office_ID', '2', 1),
(4, 'statusTrainer_ID', '1', 0),
(1, 'enabledApp', '1', 0),
(1, 'salesCode_ID_add', '1', 1),
(1, 'salesCode_ID_add', '2', 1),
(1, 'salesCode_ID_add', '3', 1),
(1, 'salesCode_ID_add', '20', 1),
(1, 'salesCode_ID_process', '2', 1),
(1, 'salesCode_ID_process', '3', 1),
(1, 'salesCode_ID_process', '20', 1);

I would like to run this query for data filtering

SELECT `user_ID`
FROM (`user_settings`)
WHERE (
`item` =  'enabledApp'
 AND 
`value` IN ('1') 
)
 AND 
(
`item` =  'statusTrainer_ID'
 AND 
`value` IN ('0') 
)

I am expecting '3' as result but MYSQL returns an empty set. why?? thanks for answer.

UPDATED

I solved it by subquery

SELECT distinct `user_ID`
FROM (`user_settings`)
WHERE user_ID IN(
SELECT `user_ID`
FROM (`user_settings`)
WHERE (
item =  'enabledApp'
 AND 
value IN (1) 
))

AND user_ID IN(SELECT `user_ID`
FROM (`user_settings`)
WHERE (
item =  'statusTrainer_ID'
 AND 
value IN (0) 
))

Demo

but it's little complicated...is there some cleaner solution????

yes your first query returns: 3 3 1, second EMPTY, third EMPTY — NoiseManiac
– NoiseManiac, Commented Jun 22, 2012 at 15:30
yes...if I run conditions separately it works but together with AND not — NoiseManiac
– NoiseManiac, Commented Jun 22, 2012 at 15:42

Fahim Parkar · Accepted Answer · 2012-06-22 16:12:12Z

1

You need an or:

SELECT `user_ID`
FROM (`user_settings`)
WHERE ( `item` =  'enabledApp'  AND  `value` IN ('1')  )  OR
      ( `item` =  'statusTrainer_ID'  AND  `value` IN ('0')  )

It returned the empty set because item cannot be both "enabledApp" and "statusTrainer_ID".

Oh, you are trying to get user ids that have both. Do it this way:

SELECT `user_ID`
FROM `user_settings`
GROUP BY `user_ID`
HAVING max(case when `item` =  'enabledApp'  AND  `value` IN ('1') then 1 else 0 end) = 1 and
       max(case when  `item` =  'statusTrainer_ID'  AND  `value` IN ('0') then 1 else 0 end) = 1

Demo

edited Jun 22, 2012 at 16:12

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

answered Jun 22, 2012 at 15:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Fahim Parkar Over a year ago

this will not give OP what he wanted... See here

NoiseManiac Over a year ago

I don't understand why can not be both items if they are separated by brackets and the condition statement within brackets get precedence and executed first and together or not???.

Gordon Linoff Over a year ago

He has clarified what he wants, so I modified my answer. The original query is a no-brainer that it will return no rows.

Collectives™ on Stack Overflow

MYSQL returns empty result set?

UPDATED

Demo

1 Answer 1

Demo

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATED

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related