Werid database situation... performance

[Anon]

I have a little situation, and since I'm not incredibly descent at mySQL, I'm having a bit of trouble.

I'm writing a script to generate a listing of educational departments for a school. In each one of those departments displayed, I am attempting to display all the teachers involved in that department, and the courses that go along with it.

Right now im starting off by selecting all the records from the dept field.. then for each record im executing 2 queries. It first selects records from teachers where id_dept1=$deptid OR id_dept2=$deptid OR id_dept3=$deptid. The second is select from courses where id_dept=$deptid

Does anyone have a faster way to do this with more advanced SQL (Joins, etc...)? I'm having quite the time doing this. Any help would be appreciated.

[mwilson]

If you could post all of the SQL queries you are executing (perhaps renaming tables if your security conscious?) perhaps [we] could provide a working SQL statement for you? That would certainly make life easier anyway.

Matt

[Anon]

$request -> SELECT * FROM {$db_prefix}departments ORDER BY title ASC

that is the first.. then while($row = mysql_fetch_array($request) )

$request2 -> SELECT id_teacher,name FROM {$db_prefix}teachers WHERE id_dept1=$dept_id_dept OR id_dept2=$dept_id_dept OR id_dept3=$dept_id_dept ORDER BY name ASC

There ya go :p