Edd,
Have you tried something like:-
select t1.student
, t1.college
, ...
, t5.faculty -- ie just the info for a student
where t1.student in
( select distinct student
from students@live t1,
student_modules@live t2,
programmes@live t3,
people@live t4,
cas_usernames t5,
cas_faculty_site_servers t6,
cas_servers t7
where t1.student = t5.student and
t2.student = t1.student and
t2.record_type = 'PRG' and
t2.student_module_status = 'R' and
t3.programme = t2.programme and
t3.programme_version = t2.programme_version and
t4.person_no = t1.person_no and
t6.faculty = t5.faculty and
t6.site_code = t5.site_code and
t6.server_name = t7.server_name
)
ie find the set of student ids that match your criteria and then select
the required attributes for those students.
HTH
--
MarkT
Edd Dawson wrote:
>
> Hi,
>
> I have the following query :
>
> Select t1.student,
> t1.college,
> t2.programme,
> t3.faculty,
> t4.forename,
> t4.surname,
> t5.number_of_processes,
> t5.faculty,
> t5.site_code,
> t6.context,
> t6.server_name,
> t5.username,
> t6.base_context,
> t7.server_directory,
> t2.programme_version
> from
> students@live t1,
> student_modules@live t2,
> programmes@live t3,
> people@live t4,
> cas_usernames t5,
> cas_faculty_site_servers t6,
> cas_servers t7
> where
> t1.student = t5.student and
> t2.student = t1.student and
> t2.record_type = 'PRG' and
> t2.student_module_status = 'R' and
> t3.programme = t2.programme and
> t3.programme_version = t2.programme_version and
> t4.person_no = t1.person_no and
> t6.faculty = t5.faculty and
> t6.site_code = t5.site_code and
> t6.server_name = t7.server_name
>
> i want it to return just one record for every distinct student it picks
> up regardless of what the rest of the fields contain, i have tried
> putting distinct around the t1.student column but this has no effect,
> then i tried to group by t1.student but it complained about the other
> columns.
>
> Surely there must be someway to achieve what i want, but i cant find out
> how... can anybody help?
>
> Thanks
>
> Edd
>
>
>
