Results 1 to 3 of 3

Thread: select query

  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: select query

    Hi everyone,
    I'am looking for to have a query to display: F_Nbr,F_Name and F_Rank_Desc from 3 table posted below:
    Code:
    Table Faculty
         F_NBR F_NAME
    ---------- ---------------
           119 Barb Martin
           421 Al Jones
           462 Tom Johnson
           209 Jane Newton
           485 Pam Sparks
           507 Rob Canton
           564 Tim Ferris
           683 Frank Nilsen
           710 Ramon Garcia
    
    Table Fac_Rank
         F_NBR  F_RANK_ID DATE_ATT
    ---------- ---------- ---------
           119          1 15-SEP-93
           209          1 01-SEP-98
           209          2 13-SEP-96
           421          1 15-AUG-00
           462          1 12-APR-83
           462          2 14-SEP-93
           485          1
           485          2 12-SEP-87
           485          3 20-SEP-96
           507          1 01-SEP-94
           564          1 21-MAY-89
           564          2 01-JAN-00
           683          1 11-JUN-79
           683          2 02-AUG-87
           683          3 12-JAN-96
           710          1
           710          2 15-DEC-99
    
    Table Rank
     F_RANK_ID F_RANK_DES
    ---------- ----------
             1 Asst Prof
             2 Asso Prof
             3 Prof
    and the result should look like this:
    Code:
     F_Nbr   F_Name        F_Rank_Desc
     119     Barb Martin   Asst Prof
     209     Jane Newton   Asso Prof
     421     Al Jones      Asst Prof
     462     Tom Johnson   Asso Prof
     485     Pam Sparks    Prof
     .............
     710     Ramon Garcia  Asso Prof
    I tried some queries but did not work right. Pls help.
    Code:
    SQL> select F_Nbr, F_Name, F_Rank_Desc
      2  from faculty JOIN Fac_Rank Using(F_Nbr) JOIN Rank Using(F_Rank_ID)
      3  order By Max(F_Rank_ID);
    
         F_NBR F_NAME          F_RANK_DES
    ---------- --------------- ----------
           119 Barb Martin     Asst Prof
           421 Al Jones        Asst Prof
           462 Tom Johnson     Asst Prof
           209 Jane Newton     Asst Prof
           485 Pam Sparks      Asst Prof
           507 Rob Canton      Asst Prof
           564 Tim Ferris      Asst Prof
           683 Frank Nilsen    Asst Prof
           710 Ramon Garcia    Asst Prof
           462 Tom Johnson     Asso Prof
           209 Jane Newton     Asso Prof
    
         F_NBR F_NAME          F_RANK_DES
    ---------- --------------- ----------
           485 Pam Sparks      Asso Prof
           564 Tim Ferris      Asso Prof
           683 Frank Nilsen    Asso Prof
           710 Ramon Garcia    Asso Prof
           485 Pam Sparks      Prof
           683 Frank Nilsen    Prof
    
    17 rows selected.
    
    SQL> select distinct F_Nbr, F_Name, F_Rank_Desc
      2  from faculty JOIN Fac_Rank Using(F_Nbr) JOIN Rank Using(F_Rank_ID)
      3  order By Max(F_Rank_ID);
    
         F_NBR F_NAME          F_RANK_DES
    ---------- --------------- ----------
           119 Barb Martin     Asst Prof
           421 Al Jones        Asst Prof
           462 Tom Johnson     Asst Prof
           209 Jane Newton     Asst Prof
           485 Pam Sparks      Asst Prof
           507 Rob Canton      Asst Prof
           564 Tim Ferris      Asst Prof
           683 Frank Nilsen    Asst Prof
           710 Ramon Garcia    Asst Prof
           462 Tom Johnson     Asso Prof
           209 Jane Newton     Asso Prof
    
         F_NBR F_NAME          F_RANK_DES
    ---------- --------------- ----------
           485 Pam Sparks      Asso Prof
           564 Tim Ferris      Asso Prof
           683 Frank Nilsen    Asso Prof
           710 Ramon Garcia    Asso Prof
           485 Pam Sparks      Prof
           683 Frank Nilsen    Prof
    
    17 rows selected.
    
    SQL> select F_Nbr, F_Name, F_Rank_Desc
      2  from faculty JOIN Fac_Rank Using(F_Nbr) JOIN Rank Using(F_Rank_ID)
      3  Where F_Rank_ID = (Select Max(F_Rank_ID) From Rank);
    
         F_NBR F_NAME          F_RANK_DES
    ---------- --------------- ----------
           485 Pam Sparks      Prof
           683 Frank Nilsen    Prof

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    I didn't test this, but it (or something very close) should work.

    Code:
    SELECT f.f_nbr, f.f_name, r.f_rank_des
    FROM faculty f,
              fac_rank fr,
              rank r
    WHERE    f.f_nbr = fr.f_nbr
    AND         fr.rank_id = r.rank_id
    AND         fr.date_att in
        (SELECT MAX(date_att)
          FROM fac_rank fr2
          WHERE fr2.f_nbr = f.f_nbr)
    Let me know if there are problems with this.

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    Thanks Buckeyes234. Your query works perfectly.
    Code:
    SQL> SELECT f.f_nbr, f.f_name, r.f_rank_desc
      2  FROM faculty f,
      3            fac_rank fr,
      4            rank r
      5  WHERE    f.f_nbr = fr.f_nbr
      6  AND         fr.f_rank_id = r.f_rank_id
      7  AND         fr.date_att in
      8      (SELECT MAX(date_att)
      9        FROM  fac_rank fr2
     10        WHERE fr2.f_nbr = f.f_nbr);
    
         F_NBR F_NAME          F_RANK_DES
    ---------- --------------- ----------
           119 Barb Martin     Asst Prof
           421 Al Jones        Asst Prof
           462 Tom Johnson     Asso Prof
           209 Jane Newton     Asst Prof
           485 Pam Sparks      Prof
           507 Rob Canton      Asst Prof
           564 Tim Ferris      Asso Prof
           683 Frank Nilsen    Prof
           710 Ramon Garcia    Asso Prof
    
    9 rows selected.
    Thanks again

    Regards

Posting Permissions

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