'SQL Double Select

This is my Table

sno | city | state | stateid

1   | chennai | tamilnadu | 1

2   | dindigul | tamilnadu | 1

3   | trivandrum | Kerala | 2

4   | cochin | Kerala | 2

I want to display like this (State Id should be selected on ascending, and the city should be displayed still it reaches the greater stateid )

tamilnadu

chennai

dindigul

Kerala

trivandrum

cochin

How can i achieve this ?

What i have tried so far is 

(select city from (select * from state order by statid asc))

There the inner query is running (select * from state order by stateid asc), but while i use the (select city from (innerquery).

It is throwing #1248 - Every derived table must have its own alias

How can i fix this ?

phpmysqlsql


Solution 1:[1]

(select city from (select * from state order by statid asc))

It needs the table name Where and field name with an operator. 

(select city from <TableName> WHERE <FieldName> <Operator> (select * from state order by statid asc))

Solution 2:[2]

I think this is not a proper normalized table as state_name and id are getting redundant. If there is a possibility of editing the table structure you can break the tables as following:

cities:

sno city_name state_id
1 chennai 1
2 dindigul  1
3 trivandrum 2
4 cochin 2

states:

state_id state_name
1 tamilnadu
2 kerala

Then you can use query like : 

SELECT c.city_name,s.state_name FROM cities c INNER JOIN states s ON c.state_id = s.`state_id` ORDER BY c.state_id ASC;

SQL DUMP:

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.24-log : Database - test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

/*Table structure for table `cities` */

DROP TABLE IF EXISTS `cities`;

CREATE TABLE `cities` (
  `sno` int(11) NOT NULL,
  `city_name` varchar(100) NOT NULL,
  `state_id` int(100) NOT NULL,
  PRIMARY KEY (`sno`,`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `cities` */

insert  into `cities`(`sno`,`city_name`,`state_id`) values (1,'chennai',1),(2,'dingi',1),(3,'cochin',2),(4,'efef',2);

/*Table structure for table `states` */

DROP TABLE IF EXISTS `states`;

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL,
  `state_name` varchar(100) NOT NULL,
  PRIMARY KEY (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `states` */

insert  into `states`(`state_id`,`state_name`) values (1,'tamilnadu'),(2,'kerala');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mohit S
Solution 2

Related Questions