Geek asks and answers

Joining three tables in oracle 10g

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 specific dtand clg_idin the browser and display the resultstudent name, college name and regnoon which date is stored.

For example: I will enter clg_id = 3and 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 -

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.

+3
source share
1 answer

Well, for this you actually do not need to join the three tables. I can do this 2 as follows. Please note: I used explicit, not implicit join syntax. This has been going on for several decades 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')

SQL Fiddle, .

, - , . result sname clg_name, , , clg_id.

, , . , result Payal regno 14, regno 12 student!

( ), . , , .

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

, SQL Fiddle. , :

  • .
  • int → integer.
  • student , ( ).
  • - . , , .
  • , . .
  • .
  • results. , .. , . student_id, dt, , 8.
  • , , exams exam_id results. , .
  • . , .
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 )
);

:

+10

More articles:


All Articles