Help on joining 3 tables Oracle XE 11g 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.
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&gt; select * from student;

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

SQL&gt; select * from module;

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

SQL&gt; select * from marks;

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

SQL&gt; 


SQL&gt; 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&gt;