Retrieving data from 4 tables

hi guys, i need some help in making an SQL statement.
i am really having a hard time making the ryt one so please
help!!!!!! :eek:

i'll first give an introduction.
i have this program that needs to display a data, however these
data will come from 4 tables.

(actual contents of the tables and scenarios of the program
was changed to make it easier for others to understand the
situation and the problem) :p

TABLE 1 (COLLEGES)
This table consists of the different colleges that a university has.
ie. College of Engineering, College of Law, etc

table design:
COLLEGE_NO
COLLEGE_NAME
SEM_NO

TABLE 2 (COURSES)
This table consist of the courses that a university offers.
ie. Theology, Chemistry, Algebra, etc.

table design:
COURSE_NO
SEM_NO

TABLE 3 (OFFERINGS)
This table consist of the number of course offerings of a
specific course for a specific college and semester.

table design:
COLLEGE_NO
COURSE_NO
NO_OF_OFFERING
SEM_NO

TABLE 4 (COURSE_MASTER)
Master table for the different courses available

table design
COURSE_NO
COURSE_NAME

--

There is a screen where a user can add Colleges
(ie. College of Architecture) and the data is stored in the
COLLEGES table (TABLE 1).
There is also a screen where a user can add Courses
(ie, Calculus, Programming, P.E., etc) and the data is stored
in COURSE_MASTER table (TABLE 4) and COURSES table (TABLE 2)
* this may be weird but please bare with me since i am just
immitating the actual scenario for better understanding

Now I have a 3rd screen where a user will input the number of
offering for a particular course for a specific college.
This is a sample image of the screen

ENGINEERING ARTS LAW
COURSE
Programming 3 0 0
Theology 3 3 3
Biology - - -

Now this data will be saved on the OFFERINGS table (TABLE 3)
The Colloge_No, the Course_No, the No_Of_Offering, and Sem_No
will be saved.

Now the behaviour of the program will be like this...
All the Colleges that is stored in the COLLEGES table will be
displayed as Column Headers in the screen
All the Courses that is stored in the COURSES table will be
displayed as Row Headers.
However, if you guys noticed we need to display the Course Name
and this data is not stored in COURSES table, it is stored in
COURSE_MASTER table.

Lastly we need to display the number of offerings.
this is displayed on the OFFERINGS table.
In the example, the Course Programming has 3 offerings
for the College of ENgineering and 0 offerings for Law
This data is stored in the OFFERINGS table.

If you guys noticed, that under the Course Biology,
"-" is written. This means that there is no record of
Number of Offerings yet at the OFFERINGS table

So now this is my problem, how or what SQL statement do
I need to make to retrieve all the data stored in the
OFFERINGS table, (this already contains the No of offerings,
courses, and colleges) and aside from this also retireve all
the other Colleges and Courses that is already stored
in their respective tables but may
not yet exists in the OFFERINGS table.

so this is my problem...
please help...
thnx in advance...Looks like you need to use a LEFT JOIN.
What have you got so far? (Post SQL statement)|||this is what i've done so far.

select t1.SEM_NO, t1.COURSE_NO, t2.COLLEGE_NAME, null
from COURSES t1, COLLEGES t2
where t1.SEM_NO = t2.SEM_NO and
t1.SEM_NO = 'User Input' and
and (t1.SEM_NO, t1.COURSE_NO, t2.COLLEGE_NAME)
not in (select
t4.SEM_NO, t4.COURSE_NO,t4.COLLEGE_NAME
from OFFERINGS t4
where t4.SEM_NO = 'User Input')
group by t1.COURSE_NO, t2.COLLEGE_NAME
union
select t4.SEM_NO, t4.COURSE_NO, t4.COLLEGE_NAME, t4.NO_OF_OFFERING
from OFFERINGS t4 where t4.SEM_NO = 'User Input';

This SQL statement is already OK, but I am having some problem
including the table COURSE_MASTER, so I can retrieve the
COURSE_NAME and I cant make the ORDER BY work...

any suggestions.
thnx.|||* this may be weird but please bare with me since i am just
immitating the actual scenario for better understanding
can you change your example to use the real tables?

the table designs you gave in post #1 don't make any sense

FYI it should be "please bear with me" because "please bare with me" means let's take our clothes off together|||*chuckle* :)|||already fixed the problem.

thnx for all the help.

:)|||Would you like to post your solution so that others with similar problems may benefit from it? :)