Solved

SQL Syntax, Select

Posted on 2015-01-26
7
157 Views
Last Modified: 2015-01-26
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
Comment
Question by:myvp
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40571585
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
 

Author Comment

by:myvp
ID: 40571593
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
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40571621
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:myvp
ID: 40571628
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40571649
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
 
LVL 18

Expert Comment

by:Simon
ID: 40571650
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
 

Author Comment

by:myvp
ID: 40571670
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question