Help on joining 3 tables Oracle XE 11g

join;oracleoracle-11gsqlplus

I am trying to return the first name, surname, subject and mark from the following tables:

SQL> desc students;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 STUDENT_NO                        VARCHAR2(10)
 SURNAME                           VARCHAR2(20)
 FORENAME                          VARCHAR2(20)

SQL> desc modules;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 MODULE_CODE                       VARCHAR2(8)
 MODULE_NAME                       VARCHAR2(20)


SQL> desc marks;
 Name                     Null?    Type
 ------------------------ -------- -------------------
 STUDENT_NO                        VARCHAR2(10)
 MODULE_CODE                       VARCHAR2(8)
 MARK                              NUMBER(38)

I have tried the following SQL query within SQL*Plus 

SELECT DISTINCT FORENAME AS "First Name", SURNAME, MODULE_NAME AS "Subject", MARK 
  FROM STUDENTS, MODULES, MARKS;

but this displays duplicate entries.

The following simply does not work.

SELECT st.FORENAME AS "First Name", st.SURNAME, mod.MODULE_NAME AS "Subject" 
  FROM STUDENTS st JOIN MODULES mod ON (st.STUDENT_NO = mod.STUDENT_NO);

Am I missing something? Maybe a subquery?
Please help, total database noob.

Best Answer

The first query you have used returns Cartesian product of the rows from these three tables. For details: Join

The following is the simple example to join three tables.

SQL> select * from student;

STUDENT_ID FIRSTNAME        LASTNAME
---------- -------------------- --------------------
     1 Will         Smith

SQL> select * from module;

 MODULE_ID NAME
---------- --------------------
     1 Computing

SQL> select * from marks;

STUDENT_ID  MODULE_ID       MARK
---------- ---------- ----------
     1      1         40

SQL> 


SQL> select s.firstname, s.lastname, mo.name Module_Name, m.mark 
     from student s 
         join marks m on (s.student_id=m.student_id) 
         join module mo on (m.module_id=mo.module_id);

FIRSTNAME     LASTNAME     MODULE_NAME     MARK    
------------- ------------ --------------- --------
Will          Smith         Computing      40

SQL>
Related Question