Joining three tables in oracle 10g

oracle, oracle10g, sql

Solution

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 )
);

Further Reading:

- A visual representation of joins from Coding Horror

- Join explanation from Wikipedia

- Documentation on constraints

- Documentation on create table

- Create table examples from TECH on the Net

- Comparing Dates in Oracle SQL - My own answer about dates in Oracle

Problem

Original source

Related problems

• Comparing Dates in Oracle SQL