Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2012
    Posts
    14

    Red face Unanswered: Duplicate values left out

    Hey,

    I am just a beginner to SQL programming so please be gentle...

    I am trying to write a report that shows what teaches advises, what students. I would like to show only the advisor's name once. Here is what I have:

    SELECT FACULTY_t.LastName||', '||FACULTY_T.FirstName AS "Advisor", STUDENT_T.LastName||', '||STUDENT_T.FirstName AS "Student"
    FROM STUDENT_T
    INNER JOIN FACULTY_T
    ON STUDENT_T.FacultyID = FACULTY_T.FacultyID
    ORDER BY FACULTY_t.LastName||', '||FACULTY_T.FirstName DESC

    With this the advisor's name comes up multiple times and I only want it displayed once.

    Thanks in advance for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dgerbergss View Post
    With this the advisor's name comes up multiple times and I only want it displayed once.
    it comes up multiple times because there are multiple rows for each advisor

    so it's showing this --
    Code:
    advisor tom     student mary
    advisor tom     student jane
    advisor harry   student fred
    advisor harry   student biff
    advisor harry   student todd
    what you want to show is this --
    Code:
    advisor tom  
         student mary
         student jane
    advisor harry  
         student fred
         student biff
         student todd
    however, this is not easily done with SQL (in fact, in some database systems, it's insane)

    it is a task that is far better done in your front-end application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2012
    Posts
    14
    Thanks for getting back to me. It seems like this should be so simple but I guess not :-(.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You didn't specify tool you use to create that report.

    I believe that (most) specialized tools (such as Oracle Reports Builder or iReport) support that request easily. If you use SQL*Plus, then its BREAK command might be what you are looking for:
    Code:
    SQL> break on dname
    SQL>
    SQL> select d.dname, e.ename, e.job
      2  from dept d,
      3       emp e
      4  where e.deptno = d.deptno
      5  order by d.dname, e.ename;
    
    DNAME          ENAME      JOB
    -------------- ---------- ---------
    ACCOUNTING     CLARK      MANAGER
                   KING       PRESIDENT
                   MILLER     CLERK
    RESEARCH       ADAMS      CLERK
                   FORD       ANALYST
                   JONES      MANAGER
                   SCOTT      ANALYST
                   SMITH      CLERK
    SALES          ALLEN      SALESMAN
                   BLAKE      MANAGER
                   JAMES      CLERK
                   MARTIN     SALESMAN
                   TURNER     SALESMAN
                   WARD       SALESMAN
    
    14 rows selected.
    
    SQL>

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I also think same as r937 and Littlefoot.
    It would be better to do by front-end application language and/or tool.

    But, if doing it in SQL,
    GROUP BY GROUPING SETS and GROUPING function would be useful, like...
    Code:
    SELECT CASE GROUPING(s.student_id)
           WHEN 0 THEN
                '   Sudent: ' || MAX(s.first_name)
           ELSE 'Advisor: ' || MAX(f.first_name)
           END  AS "Advisor-Student"
     FROM  student_t s
     INNER JOIN
           faculty_t f
      ON   s.faculty_id = f.faculty_id
     GROUP BY
           GROUPING SETS(
              ( f.faculty_id , s.student_id )
            , ( f.faculty_id )
           )
     ORDER BY
           MAX(f.faculty_id)
         , GROUPING(s.student_id) DESC
         , MAX(student_id)
    ;

  6. #6
    Join Date
    Jul 2012
    Posts
    14
    When you do s.student_id are you just shirting the student_t table?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    shirting????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2012
    Posts
    14
    Shorting, sorry for the typo...

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "shorting the table" mean, though?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by dgerbergss View Post
    When you do s.student_id are you just shirting the student_t table?
    I don't know what you mean by "shorting".

    Anyway, please adjust column names and grouping columns to match with your real case.

    By the way,
    Pair of (Firstname, Lastname) would be not enough to identify a person,
    if you don't want to put Mr. Pat Smith and Ms. Pat Smith into one group.
    Usually, an unique identifier(primary key: a column or combinaion of columns) should be in every tables.
    Last edited by tonkuma; 08-02-12 at 05:40. Reason: Replace "shirting" with "shorting"

Tags for this Thread

Posting Permissions

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