Jump to content

Unknown column 'CTB' in 'where clause' in MYSQL


Recommended Posts

im having a bit of trouble with an sql statement.

 

Here is the problem i have two tables which share a common field.

 

table 1 is called units and has two fields unitCode and unitName.

table 2 is called courseUnits and 2 fields unitCode and couresCode.

 

here is an sql dump of my table at the momment

 

CODE

CREATE TABLE `units` (

`unitCode` varchar(4) NOT NULL default '',

`unitName` varchar(50) NOT NULL default '',

PRIMARY KEY (`unitCode`)

) TYPE=MyISAM;

 

--

-- Dumping data for table `units`

--

 

INSERT INTO `units` VALUES ('B101', 'Proffesional Studies');

INSERT INTO `units` VALUES ('B202', 'Proffesional Studies 3');

INSERT INTO `units` VALUES ('B253', 'Network Configuration And Administration');

 

 

CODE

CREATE TABLE `courseUnits` (

`courseCode` varchar(4) NOT NULL default '',

`unitCode` varchar(4) NOT NULL default '',

PRIMARY KEY (`courseCode`,`unitCode`)

) TYPE=MyISAM;

 

--

-- Dumping data for table `courseUnits`

--

 

INSERT INTO `courseUnits` VALUES ('CS', 'B101');

INSERT INTO `courseUnits` VALUES ('CS', 'B202');

INSERT INTO `courseUnits` VALUES ('CS', 'B253');

INSERT INTO `courseUnits` VALUES ('CTB', 'B101');

INSERT INTO `courseUnits` VALUES ('CTB', 'B202');

INSERT INTO `courseUnits` VALUES ('CTB', 'B251');

INSERT INTO `courseUnits` VALUES ('ITB', 'B101');

INSERT INTO `courseUnits` VALUES ('ITB', 'B253');

 

 

What i want the search to do is for example if i want to list all unitnames for the course CTB. I have the following sql statement.

 

SELECT units.unitName, units.unitCode FROM units, courseUnits WHERE courseUnits.courseCode = CTB

 

however this gives the error

 

Unknown column 'CTB' in 'where clause'

 

Can anyone tell me what is wrong with this sql statement. been looking at it four 3 hours. Once ive done this last bit my work will finally be done.

Link to comment
Share on other sites

SELECT units.unitName, units.unitCode FROM units, courseUnits WHERE units.unitCode = courseUnits.unitCode AND courseUnits.courseCode = "CTB"

 

Non numeric values must be quoted. Also i think you need to associate the two tables common fields in your WHERE clause "units.unitCode = courseUnits.unitCode".

It's early morning here and i have not had my cup of tea yet so it may or may not work ;)

Edited by johnnyv
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...