Relational Join - Dev Articles

Dev Articles Community Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

December 9th, 2004, 08:19 AM

Jon R

Registered User

Join Date: Dec 2004

Posts: 4

Time spent in forums: < 1 sec
Reputation Power: 0

Relational Join

Hi,

I am trying to do a relational join using two tables (STUDENT and LECTURER) using the relation table TEACHES.

The SQL I am using for the join is:

SELECT student.student_name, lecturer.lecturer_name, lecturer.subject
FROM lecturer l, student s
WHERE s.lecturer = l.lecturer_name;

The three tables that I have are:

Lecturer SQL

SET AUTOCOMMIT IMMEDIATE;

CREATE TABLE lecturer (

staff_number NUMBER(3) PRIMARY KEY,

lecturer_name CHAR(20),

gender CHAR(6),

dob DATE,

subject CHAR(20),

years_employed NUMBER(2));

INSERT INTO lecturer VALUES (1, 'Martin Lee', 'Male', '26-Sep-1960', 'English', 18);

INSERT INTO lecturer VALUES (2, 'Greg Simpson-Horn', 'Male', '16-Aug-1971', 'Geography', 12);

INSERT INTO lecturer VALUES (3, 'Ross Kemp', 'Male', '16-Jul-1976', 'Science', 8);

INSERT INTO lecturer VALUES (4, 'Graham Lee', 'Male', ‘26-Aug-1962', 'Geography', 13);

INSERT INTO lecturer VALUES (5, 'Liz Smith', 'Female', ‘12-Dec-1967', 'Science', 14);

INSERT INTO lecturer VALUES (6, 'Andy Smith', 'Male', '14-Apr-1982', 'Computing', 2);

INSERT INTO lecturer VALUES (7, 'Anna Michael', 'Female', '23-Sep-1975', 'Geography', 6);

Student SQL

SET AUTOCOMMIT IMMEDIATE;

CREATE TABLE student (

student_number NUMBER(3) PRIMARY KEY,

student_name CHAR(20),

gender CHAR(6),

dob DATE,

lecturer CHAR(30),

exam CHAR(20),

academic_year NUMBER(2));

INSERT INTO student VALUES (1, 'Jon Russell', 'Male', '26-Aug-1981', 'Anna Michael', 'Postgraduate', 1);

INSERT INTO student VALUES (2, 'Joe Hodgetts', 'Male', '16-Dec-1972', 'Liz Smith', 'PhD', 4);

INSERT INTO student VALUES (3, 'Dave Ross', 'Male', '12-Nov-1981', 'Graham Lee', 'Undergraduate', 3);

INSERT INTO student VALUES (4, 'Sharla Picken', 'Female', '06-Apr-1982', 'Ross Kemp', 'Undergraduate', 3);

INSERT INTO student VALUES (5, 'Emma Bagnal', 'Female', '04-Mar-1970', 'Martin Lee', 'Postgraduate', 2);

INSERT INTO student VALUES (6, 'Kate Broadhurst', 'Female', '12-Jul-1985', 'Martin Lee', 'GCSE', 3);

Teaches (Relationship) SQL

SET AUTOCOMMIT IMMEDIATE;

CREATE TABLE teaches (

staff_number NUMBER(3) REFERENCES

russelljd.lecturer(staff_number),

student_number NUMBER(3) REFERENCES

russelljd.student(student_number),

PRIMARY KEY (staff_number, student_number));

INSERT INTO teaches VALUES (7, 1);

INSERT INTO teaches VALUES (5, 2);

INSERT INTO teaches VALUES (4, 3);

INSERT INTO teaches VALUES (3, 4);

INSERT INTO teaches VALUES (1, 5);

INSERT INTO teaches VALUES (1, 6);

But SQL is complaining that:...

"SQL> select student.student_name, lecturer.lecturer_name, lecturer.subject
2 from lecturer l, student s
3 where s.lecturer = l.student;
where s.lecturer = l.student
*
ERROR at line 3:
ORA-00904: "L"."STUDENT": invalid identifier"

Please could you help!!!
Thanks
Jon

December 10th, 2004, 11:08 AM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

Your Lecturer table doesn't have a field called "Student".
Your Student table doesn't have a field called "Lecturer"

Perhaps you mean to be using the "Teaches" table instead of Lecturer?

You might want to try something like this:

Code:

SELECT student.student_name, lecturer.lecturer_name, lecturer.subject
FROM lecturer, student, teaches
WHERE teaches.student_number = teaches.student_number
AND teaches.staff_number = lecturer.staff_number;

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: