CODEWITHSUNDEEP
mysqlsubquery
shamelesshacker
shamelesshacker

Reputation: 185

Subquery does not work, but substituting subquery results does

Okay, I am using the states table found here

...AND A TABLE default_item defined:

CREATE TABLE IF NOT EXISTS `default_item` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `States` varchar(200) NOT NULL DEFAULT '""'
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=637 ;

INSERT INTO `default_Item` (`id` `States`) VALUES
(636, '''AR'',''CA''');

I have a field in a table that contains a list of State abbreviations, the field is "States", so if I select one of those fields as example:

SELECT `States`
FROM `default_item` 
WHERE `id`=636

This returns via PHPMyAdmin:

'AR','CA'

I want to be able to replace the state abbreviations with the state names from the table 'state' cited above. If I use:

SELECT group_concat( `state` )
FROM `state`
WHERE `state_abbr`
IN (
'AR', 'CA'
)

It works! I get:

Arkansas,California

But if I use the subquery...

SELECT group_concat( `state` )
FROM `state`
WHERE `state_abbr`
IN (
SELECT `States`
FROM `default_item` 
WHERE `id`=636
)

...this returns NULL.

Have been searching for some hint as to what I am doing wrong, but cannot seem to find the trick. Anyone have a solution?

Upvotes: 0

Views: 144

Answers (2)

merakli
merakli

Reputation: 203

Try this

SELECT group_concat( `state` )
FROM `state`
WHERE `state_abbr`
IN (
SELECT `States` FROM `default_item` WHERE `id`='636')

'636'

you can ask here alive

http://sqlfiddle.com/#!2/7bd88/4

where is table default_item

Upvotes: 1

zmbq
zmbq

Reputation: 39023

Your first query returns a string "AR, CA" and not a list of two strings. Your compound query when substituting the subquery actually looks like this:

SELECT group_concat( `state` )
FROM `state`
WHERE `state_abbr`
IN (
  "AR, CA"
)

That's why it doesn't work.

Upvotes: 1

Related Questions