home, host university link problem

[graham262]

Hi,

 

I've got a table "students" with the following fields {student_name, home_university, host_university}, both home and host universities are ID that refer to a "university" table containing {id, name, city} for example

 

What I want to do is pull out from one query the student ID and the name of their home and host university. To do this I need to link both home and host uni id to the university table but am not sure how to code this in MySQL. If it was just the home uni name I was after I would do this:

 

SELECT students.name, universities.name

FROM students, universities

WHERE students.home_university=university.id

 

What if I want to display a list of results listing both home AND host uni names and then perhaps list by either name alphabetically?  ???

[bubblegum.anarchy]

Read this: http://dev.mysql.com/doc/refman/4.1/en/join.html

 

and then

 

Read the ORDER BY information here: http://dev.mysql.com/doc/refman/4.1/en/select.html