Solved

SQL Syntax, Select

Posted on 2015-01-26
7
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
paypal ipn to mysql 3 105
mysql db 3 95
MySql Recovery 2 41
Fatal error: Uncaught Error: Call to undefined function msql_num_rows() with PHP 7.0 8 41
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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