riya riya - 5 months ago 29
MySQL Question

Nested query not working

Sub query does not work error occurs Unknown column 'state' in 'field list' but state column is available. This is error of sub query.

//query
SELECT `country` ,GROUP_CONCAT(`state` separator ",") as a
FROM (

SELECT `country` , CONCAT( `state` , ':', GROUP_CONCAT( DISTINCT `district`
ORDER BY `district` ASC
SEPARATOR ',' ) ) AS NAME
FROM `temp_location`
GROUP BY `country` , `state`
) AS result
GROUP BY `country`


Is any other way to work out this query?

i solved this query by changing name to state
when i am trying to used this query in codeigniter like this

$this->db->select('country ,GROUP_CONCAT(state SEPARATOR "//" ) AS a',false);
$this->db->from('( select country,concat(state, ":", GROUP_CONCAT(district separator ",")) as state from temp_location
group by country, state) result');
$this->db->group_by('country')


i have get following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'state)
state) GROUP BY
country`' at line 2

Answer

You have no state column in your sub query, try this;)

SELECT `country` ,GROUP_CONCAT(`NAME` separator ",") as a
FROM (

SELECT `country` , CONCAT( `state` , ':', GROUP_CONCAT( DISTINCT `district`
ORDER BY `district` ASC
SEPARATOR ',' ) ) AS NAME
FROM `temp_location`
GROUP BY `country` , `state`
) AS result
GROUP BY `country`

Edited

select `country` ,GROUP_CONCAT(`state` SEPARATOR "//" ) AS `a`
from (
    select
        `country`,
        concat(`state`, ":", GROUP_CONCAT(`district` separator ",")) as `state`
    from `temp_location` 
    group by `country`, `state`) `result`
group by `country`