I have noooo idea, plz help!

[rubric]

I have a problem with getting a join to work with TWO foreign keys on the same table. Here is what's going on:

I have a table called "profiles". In there is a bunch of data and two foreign keys called "countryID" and "nationalityID". Both of these keys point to a table called "countries". "countryID" is where the person currently lives and "nationalityID" is country of nationality. They don't necessarily have to be the same. A profile can state that a person is Australian but lives in England.

Now, how do I make a select statement that can join "profiles" and "countries" and extract two different countries depending on the keys in ONE ROW?

Ex.

Profile #1
id = 1
countryID = 2
nationalityID = 5
...

Countries:
1 = Canada
2 = England
3 = Brazil
4 = U.S.
5 = Australia

I want to join the profile #1 with the countries table and get England out as the country of residence and Australia out as country of nationaliy in one row.

I tried searching for it, but no luck...

Thanks.

p.s. I'm using MySQL 4.0.18.

[rubric]

Sorry guys, I just figured it out.

You do a double join on the countries table but with different names.

SELECT p.id, c1.name AS location, c2.name AS nationality
FROM profiles p JOIN countries c1 ON p.countryID = c1.id JOIN countries c2 ON p.nationalityID = c2.id
WHERE p.id = 1

Unless you have a better solution, thanks for viewing this thread.

: ) sorry...