• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

what wrong with mysql statement

#1064 - 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 '*) from member, amphur Where amphur.AMPHUR_CODE = substring(member.own,3,4) g' at line 1
select   substring(member.own,3,4),  count (*)
 from member, amphur 
Where amphur.AMPHUR_CODE  = substring(member.own,3,4)
group by substring(member.own,3,4)

Open in new window

0
teera
Asked:
teera
3 Solutions
 
tigin44Commented:
try this


select   substring(member.`own`,3,4),  count (*)
 from member
 inner join  amphur on amphur.AMPHUR_CODE  = substring(member.`own`,3,4)
group by substring(member.`own`,3,4)
0
 
jimyXCommented:
I am not sure but let's try this one please:
select  substring(member.own,3,4) as Col1,  count (*) as Col2
 from member, amphur
Where amphur.AMPHUR_CODE  = substring(member.own,3,4)
group by Col1
0
 
teeraAuthor Commented:
select   substring(member.own,3,4),  count(*),amphur.AMPHUR_NAME
 from member, amphur
Where amphur.AMPHUR_CODE  = substring(member.own,3,4)
group by substring(member.own,3,4)


#1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
0
 
teeraAuthor Commented:
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 ;
0
 
SharathData EngineerCommented:
try this.
SELECT SUBSTRING(m.own,3,4), 
         COUNT(* ) AS cnt 
    FROM MEMBER AS m, 
         amphur AS a 
   WHERE a.AMPHUR_CODE COLLATE utf8_general_ci = SUBSTRING(m.own,3,4) 
GROUP BY SUBSTRING(m.own,3,4)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now