We help IT Professionals succeed at work.

sql statement mysql

teera
teera asked
on
Medium Priority
243 Views
Last Modified: 2012-05-11
what wrong with code below when i fill (select  amphur.AMPHUR_NAME where c = SUBSTRING(m.own,3,4)  ) it error

CREATE TABLE IF NOT EXISTS `member` (
  `idmember` int(11) NOT NULL AUTO_INCREMENT,
  `own` varchar(255) DEFAULT NULL,
  `Sname` varchar(255) DEFAULT NULL,
  `Ssurname` varchar(255) DEFAULT NULL,
  `Sex` enum('¿','¿') NOT NULL,
  `Hno` varchar(255) DEFAULT NULL,
  `Moo` varchar(255) DEFAULT NULL,
  `tambol` varchar(200) NOT NULL,
  `dateofbirth` date DEFAULT NULL,
  `migratedate` date DEFAULT NULL,
  `status` enum('5','4','3','2','1') DEFAULT '5',
  `Unit` int(4) DEFAULT NULL,
  `staff1` int(11) DEFAULT NULL,
  `staff2` int(11) DEFAULT NULL,
  `fathercode` varchar(30) NOT NULL,
  `mathercode` varchar(30) NOT NULL,
  PRIMARY KEY (`idmember`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8994 ;


CREATE TABLE IF NOT EXISTS `amphur` (
  `AMPHUR_ID` int(5) NOT NULL AUTO_INCREMENT,
  `AMPHUR_CODE` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `AMPHUR_NAME` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `GEO_ID` int(5) NOT NULL DEFAULT '0',
  `PROVINCE_ID` int(5) NOT NULL DEFAULT '0',
  `province_name` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`AMPHUR_ID`),
  KEY `province_name` (`province_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=999 ;
SELECT SUBSTRING(m.own,3,4) as c , (select  amphur.AMPHUR_NAME where c = SUBSTRING(m.own,3,4)  ),
         COUNT(* ) AS cnt 
    FROM MEMBER AS m 
         
   GROUP BY SUBSTRING(m.own,3,4)
order by cnt desc

Open in new window

Comment
Watch Question

Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
select  amphur.AMPHUR_NAME from amphur
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.