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"
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.
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:
SQL> break on dname
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
RESEARCH ADAMS CLERK
SALES ALLEN SALESMAN
14 rows selected.
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...
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
ON s.faculty_id = f.faculty_id
( f.faculty_id , s.student_id )
, ( f.faculty_id )
, GROUPING(s.student_id) DESC
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"