Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    54

    Unanswered: 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!!!!!!

    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)

    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....

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Looks like you need to use a LEFT JOIN.
    What have you got so far? (Post SQL statement)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Oct 2004
    Posts
    54
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    * 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *chuckle*
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2004
    Posts
    54
    already fixed the problem.

    thnx for all the help.



  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Would you like to post your solution so that others with similar problems may benefit from it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •