CODEWITHSUNDEEP
phpmysql
Lou
Lou

Reputation: 4474

Unknown column in `where` clause

I am getting the error described in title:

Unknown column 'FeedbackType' in 'where clause'

But I don't understand why. This is my query:

SELECT SQL_CALC_FOUND_ROWS `Appointments`.ID, FeedbackType, FeedbackSubType 
FROM `UserFeedback`
INNER JOIN `Appointments` ON `Appointments`.ID = `UserFeedback`.Appointments_ID 
INNER JOIN `Reasons` ON `UserFeedback`.FeedbackSubType = `Reasons`.ID  
WHERE `FeedbackType` = 1  ORDER BY `Appointments`.ID ASC
LIMIT 0, 10

FeedbackType is a column in the UserFeedback table, casing is correct, checked it several times already.

For completeness, this is the table schema:

CREATE TABLE IF NOT EXISTS `UserFeedback` 
(
   ID bigint(20) NOT NULL AUTO_INCREMENT,
   FeedbackType int(4) NOT NULL,
   FeedbackSubType int(4) NOT NULL,
   Notes varchar(170) NULL,
   Appointments_ID bigint(20) NOT NULL,
   IpTracking_ID bigint(20) NOT NULL,
   PRIMARY KEY (ID),
   FOREIGN KEY (Appointments_ID) REFERENCES Appointments(Id), 
   FOREIGN KEY (IpTracking_ID) REFERENCES IpTracking(Id)    
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;

What could be the issue?

[Edit]

These variants don't work either (because FeedbackType does not contain reserved words/characters and belongs only to the UserFeedback table):

... WHERE UserFeedback.FeedbackType = 1
... WHERE `UserFeedback`.`FeedbackType` = 1
... WHERE FeedbackType = '1'
etc.

(and I actually see no reason why they should)

[Edit 2]

I ran SELECT * FROM UserFeedback to make sure it really contains the column, and I got several rows, all containing the column (well, INSERTs worked without errors).

For each of the mentioned variants, I always get the same error, always in the WHERE clause. If I omit the WHERE clause, I get unfiltered results (including the FeedbackType column in those results), so it's really confusing.

[Solution]

For some reason, replacing the WHERE query with a condition inside INNER JOIN fixed it, as @MarinSagovac suggested in his second snippet:

SELECT SQL_CALC_FOUND_ROWS `Appointments`.ID, FeedbackType, FeedbackSubType 
FROM `Appointments`
INNER JOIN `UserFeedback` ON `Appointments`.ID = `UserFeedback`.Appointments_ID 
   AND `UserFeedback`.FeedbackType = 1
INNER JOIN `Reasons` ON `UserFeedback`.FeedbackSubType = `Reasons`.ID  
ORDER BY `Appointments`.ID ASC
LIMIT 0, 10

Note that there's no WHERE clause now, but the semantics should be the same, right? And it's clear that the column really exists, so the error message is a bit misleading IMHO.

Upvotes: 2

Views: 2158

Answers (5)

Goran Štuc
Goran Štuc

Reputation: 581

Foreign key while using myisam?

that's probably the problem

Upvotes: 0

phsaires
phsaires

Reputation: 2378

Try this in WHERE clause:

WHERE UserFeedback.FeedbackType = '1'

Remove all quotes(') in field's name

Upvotes: -2

Marin Sagovac
Marin Sagovac

Reputation: 3972

Try add backtick:

SELECT SQL_CALC_FOUND_ROWS `Appointments`.ID, `FeedbackType`, `FeedbackSubType`
FROM `UserFeedback`
INNER JOIN `Appointments` ON `Appointments`.ID = `UserFeedback`.Appointments_ID 
INNER JOIN `Reasons` ON `UserFeedback`.FeedbackSubType = `Reasons`.ID  
WHERE `FeedbackType` = 1  ORDER BY `Appointments`.ID ASC
LIMIT 0, 10

Added try:

SELECT SQL_CALC_FOUND_ROWS `Appointments`.ID, FeedbackType, FeedbackSubType 
FROM `UserFeedback`
INNER JOIN `Appointments` ON `Appointments`.ID = `UserFeedback`.Appointments_ID 
INNER JOIN `Reasons` ON `UserFeedback`.FeedbackSubType = `Reasons`.ID  
INNER JOIN `UserFeedback`.`FeedbackType` = 1  ORDER BY `Appointments`.ID ASC
LIMIT 0, 10

Upvotes: 1

Sourabh
Sourabh

Reputation: 1765

SELECT SQL_CALC_FOUND_ROWS `Appointments`.ID, UserFeedback.FeedbackType, `UserFeedback.FeedbackSubType 
FROM `UserFeedback'
INNER JOIN `Appointments` ON `Appointments`.ID = `UserFeedback`.Appointments_ID 
INNER JOIN `Reasons` ON `UserFeedback`.FeedbackSubType = `Reasons`.ID  
WHERE UserFeedback.FeedbackType = 1  ORDER BY `Appointments`.ID ASC
LIMIT 0, 10

try it now

Upvotes: 0

FSP
FSP

Reputation: 4827

Did you try UserFeedback.FeedbackType in the where clause?

Upvotes: 2

Related Questions