SQL Syntax, Select

I have a simple query that I'm getting an error?

SELECT e.EmpID,  g.PHONE
FROM gnrl g
LEFT JOIN emp e ON (e.EMP_ID = g.EMP_ID)
WHERE g.CAT.G_ID = 1
AND g.ITEM.G_ID = 1
AND e.EMP_ID = 5054;

UNKNOWN COLUMN 'g.CAT.G_ID' IN 'where clause
myvpAsked:
Who is Participating?
 
SimonConnect With a Mentor Commented:
Because the column names have periods in them, wrap them with backticks in your query. If it's a table you've only just created, consider renaming the columns.

SELECT e.EmpID,  g.PHONE
FROM gnrl g
LEFT JOIN emp e ON (e.EMP_ID = g.EMP_ID)
WHERE g.`CAT.G_ID` = 1
AND g.`ITEM.G_ID` = 1
AND e.EMP_ID = 5054;

See MySQL reference on Schema Object Names
and Reserved Words
0
 
SimonCommented:
Check for typo on column name?

Remove the WHERE CLAUSE to start with or do a separate query

SELECT  * from gnrl

Open in new window


To check on the column names.
0
 
myvpAuthor Commented:
It isn't a typo.


Create Table

CREATE TABLE `gnrl` (
  `GNRL_ID` mediumint(8) NOT NULL AUTO_INCREMENT,
  `EMP_ID` mediumint(8) NOT NULL,
  `CAT.G_ID` mediumint(8) NOT NULL,
  `ITEM.G_ID` mediumint(8) NOT NULL,
  `PHONE` varchar(50) DEFAULT NULL,
  `EMAIL` varchar(50) DEFAULT NULL,
  `GNAME` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`GNRL_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
myvpAuthor Commented:
No change

SELECT e.EmpID, g.PHONE
FROM gnrl g
LEFT JOIN emp e ON (e.EMP_ID = g.EMP_ID)
WHERE `g.CAT.G_ID` = 1
AND `g.ITEM.G_ID` = 1
AND e.EMP_ID = 5054;

Unknown column 'g.CAT.G_ID' in 'where clause'
0
 
Daniel WilsonCommented:
No points please ... Simon gave you the right answer.

You didn't put your backticks where Simon said to.

WHERE g.`CAT.G_ID` = 1
is not
WHERE `g.CAT.G_ID` = 1
0
 
SimonCommented:
I tried it in phpmyadmin.

It works for me like this:
SELECT e.EmpID, g.PHONE
FROM gnrl g
LEFT JOIN emp e ON (e.EmpID = g.EMP_ID)
WHERE g.`CAT.G_ID` = 1 and g.EMP_ID=1 and g.`ITEM.G_ID`=1
0
 
myvpAuthor Commented:
Thanks
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.

All Courses

From novice to tech pro — start learning today.