Solved

SQL Syntax, Select

Posted on 2015-01-26
7
153 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:SimonAdept
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:
SimonAdept 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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:SimonAdept
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now