Here is my exact sql as executed:
-----------------------------------
This works
SELECT employee.empl_num
FROM employee
JOIN job_history
ON job_history.empl_num = employee.empl_num
--------------------------------
This does not work
SELECT employee.empl_num
FROM employee
JOIN job_history
ON job_history.empl_num = employee.empl_num
AND job_history.last_date = (
SELECT MAX(j1.last_date)
FROM job_history AS j1
WHERE j1.empl_num = employee.empl_num )
---------
Here is my error message
Error : SQL logic error or missing database
no such column: employee.empl_num
---------
Here is my table layout
#
# Table structure for table: employee
#
CREATE TABLE employee ( empl_num INT(10) NOT NULL PRIMARY KEY, dept_id
VARCHAR(4) NOT NULL DEFAULT 'WMKR', username VARCHAR(16) NOT NULL,
first_name VARCHAR(22), middle_name VARCHAR(22), last_name VARCHAR(22),
addr1 VARCHAR(22), addr2 VARCHAR(22), city VARCHAR(22), state CHAR(3), zip
VARCHAR(10), country VARCHAR(4) NOT NULL DEFAULT 'USA', phone VARCHAR(17),
e_mail VARCHAR(40), password VARCHAR(16) NOT NULL, password_hint VARCHAR(50)
NOT NULL, dial_log_id VARCHAR(100) NOT NULL, pass_id VARCHAR(20) NOT NULL );
#
# Table structure for table: job_history
#
CREATE TABLE job_history ( job_num INT, empl_num INT , last_date DATE );
# --------------------------------------------------------
Igor Tandetnik wrote:
>
> kokenge <[EMAIL PROTECTED]> wrote:
>> This is such a simple SQL statement. So sorry for the question, but I
>> can't get it to work.
>> I'm trying to get a list of employees and the last time they worked
>> on a job.
>> FIles are.
>> employee file : with empl_num = employee number
>> job_history file : with empl_num, job_num, and last_date = last date
>> the employee worked on a job
>> Each employee has worked many jobs during his employment . so
>> employee to job_history is 1 to many
>> The sql is very simple and for some reason I keep getting a error
>> saying the it can't reference stuff in the subselect to the file in
>> the Select????? I have it working in all my other databases.
>> -----------------------------------------------------
>> SELECT *
>> FROM employee
>> JOIN job_history
>> ON job_history.empl_num = employee.empl_num
>> AND job_history.last_date = (SELECT max(j1.last_date)
>> FROM job_history as j1
>> WHERE j1.empl_num = employee.empl_num)
>> ---------------------------------------------------------
>> Just to simple - so what am I doing wrong????
>
> I don't get any syntax errors for this statement. The problem must be in
> something you don't show. Quote the exact error message.
>
> Igor Tandetnik
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
>
--
View this message in context:
http://www.nabble.com/subselect-tf3327306.html#a9252497
Sent from the SQLite mailing list archive at Nabble.com.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
