Problem arises in a SQL statement

[heshanm]

Hi All,

I have a samll problem related to a specific query. It creates following error. Unknown column 'attendance_state.state' in 'field list'

$query="SELECT attendance_state.state,attendance_state.state_id from attendance_state inner join attendance on attendance_state.state_id=attendance.state_id where attendance.admission_no='".$admission_id[$i]."' and attendance.date='$date'";
$result=mysql_query($query) or die (mysql_error());

The table related to the query looks like this. "state_id" is set as auto increment field.

attendance_state (state_id, state)
attendance (admission_no, state_id, date)

[smantscheff]

Show your CREATE TABLE statement.

[heshanm]

Here it is..I have used WAMP and PHPMyAdmin to create database and tables.

CREATE TABLE `attendance_state` (
  `state_id` int(20) NOT NULL auto_increment,
  `state` varchar(20) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`state_id`)
)

[urtrivedi]

same query works here fine