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

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
0
myvp
Asked:
myvp
  • 3
  • 3
1 Solution
 
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
 
SimonCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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