Teh problem is that when the query returns more than 1 row for a particular student, You have to tell SQL which one you want.
You idea of using group by could work, but you will have to use group functions for the other fields. Suppose your query returns something like: Student1 programme1 Faculty1 .... Student1 programme2 Fauclty1 .... You could use SELECT t1.student, MAX(t2.programme), MAX(t3.faculty), ... FROM ... WHERE ... GROUP BY t1.student This would give you 1 row per student, with the associated fields group according to the function you use. Out of the 6 group functions (MAX, MIN, SUM, AVG, COUNT, STDDEV) only MAX, MIN and COUNT may be used on character or date columns. If you want to see an actual value, use MAX or MIN. HTH, Jena-Claude. -----Original Message----- From: Edd Dawson [mailto:[EMAIL PROTECTED]] Sent: January 25, 2002 8:50 AM To: [EMAIL PROTECTED] Subject: SQL query 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
