Joining three tables in oracle 10g

Question

I have 3 tables, College, Student and Result

create table college
(
clg_id int,
clg_name varchar2()50,
insert into college values(1,'GIFT');
insert into college values(2,'GITA');
insert into college values(3,'MIT');

create table student
(
clg_id int,
regno int,
sname varchar2(50)
)

insert into student values(1,10,sahar);
insert into student values(2,11,raj);
insert into student values(3,12,Payal);
insert into student values(3,13,Monalisha);
insert into student values(2,14,mary);

create table Result
(
clg_id int,
sname varchar2(50),
clg_name varchar2(50),
regno int,
dt date,
result varchar2(30)
)

insert into Result values(1,'sahar',10,'GIFT',20-02-1990,'A+');
insert into Result values(2,'raj',11,'GITA',21-02-1991,''B+);
insert into Result values(3,'monalisha',13,'MIT',22-09-2005,'A++');
insert into Result values(3,'payal',14,'MIT',22-09-2005,'C');

I want to give a particular dt and clg_id from the browser and to display the result, student name, college name and the regno on which date the result has been stored.

For example: I will enter clg_id = 3 and dt = 22-09-2005 then it should display:

 clg_name   sname      regno    result      
 MIT       monalisha   13        A++     
 MIT       payal        14       C

I tried a lot... one of my attempts is

SELECT college.clg_name,student.sname,
student.regno result.result  FROM college,student,result
WHERE college.clg_id=student.clg_id=result.date;

but it's wrong... Please help.

score 10 · Accepted Answer · edited May 23 '17 at 11:46

Okay, in order to do this you don't actually need to join on 3 tables. I can be done on 2 as follows. Please note I've used the explicit as opposed to implicit join syntax. This has been around for a few decades now and should really be used.

select r.clg_name, s.sname, r.regno, r.result
  from result r
  join student s
    on r.regno = s.regno
 where r.clg_id = 3
   and r.dt = to_date('22-09-2005','dd-mm-yyyy')

I have also created a SQL Fiddle to demonstrate this.

It's possible to do this in two joins because you have partially de-normalised your database and are duplicating information, which could result in inconsistencies. In the table result there is no need for the columns sname or clg_name, and, if a student can only be registered at one college there is no need for clg_id either.

By removing these columns you ensure that the database does not allow data to be entered incorrectly and save yourself a lot of hassle in the long run. A really good example of this is your schema creation, where in the result table Payal is regno 14, but is regno 12 in your student table!

If you were to normalise your database properly ( and correcting the above mistake ) your query would be as follows. Please note that I've changed some of your naming conventions so that things are slightly less confusing.

select c.name, s.name, s.id, r.result
  from colleges c
  join students s
    on c.id = s.college_id
  join results r
    on s.id = r.student_id
 where c.id = 3
   and r.dt =  to_date('22-09-2005','dd-mm-yyyy')

Here is the schema for that query, also in a SQL Fiddle. Please note the changes I've made to your own:

Standardised naming convention.
int --> integer.
There are multiple students in the student table so I've pluralised the name ( and the others ).
Primary and foreign key constraints - very important to maintain integrity. A result, must belong to a student, who must belong to a college.
Please also note that you were inputting dates incorrectly. Never rely on implicit conversion and always do it explicitly.
Removal of unnecessary columns.
I've added a primary key to the results table. Though this is a surrogate key, i.e. it bears no relation to the data, a table should always have a primary key. You may want to add a unique constraint on student_id, dt but I would prefer the option outlined in point 8.
I would have thought you would have required yet another table, exams, and have exam_id as a foreign key in results. I haven't added this but it's something to think about.
Student name has been increased to the maximum. You just can't tell with names and it's best not to be overly restrictive.

create table colleges
(
    id integer,
    name varchar2(50),
    constraint pk_colleges primary key ( id )
  );

create table students
(
    id integer,
    name varchar2(4000), -- Use the maximum. Names are impossible to predict
    college_id integer,
    constraint pk_students primary key ( id ),
    constraint fk_student_college foreign key ( college_id )
       references colleges ( id )
);

create table Results
(
    id integer,
    student_id integer,
    dt date,
    result varchar2(30),
    constraint pk_results primary key ( id ),
    constraint fx_result_student foreign key ( student_id )
       references students ( id )
);

Joining three tables in oracle 10g

1 Answers1

Further Reading: