It's easy Smiley

SELECT c.ContactID, c.ContactName, d1.DepartmentName as MainDepartmentName, d2.DepartmentName as SecondDepartmentName
FROM contacts c
INNER JOIN departments d1 ON d1.DepartmentID = c.MainDepartment
INNER JOIN departments d2 ON d2.DepartmentID = c.SecondDepartment


Smiley just use the alias and you can SELECT the same table as many times you need, just give it a diferent name (I'm using letters, but you can use all words, like departm01 or contacts, just with letters the code it's much simple to read)

Bruno Alexandre
"a Portuguese in KĂžbenhavn"

Posted by sbc // Thu, Apr 6, 2006 7:49 AM


Say I have a table of departments:

departments
-----------
DepartmentID    DepartmentName
------------------------------
1               Department 1
2               Department 2

And a table as follows:

contacts
--------
ContactID   ContactName     MainDepartment   SecondDepartment
-------------------------------------------------------------
1           Fred Bloggs     1                2
2           Bob Smith       2                2

How would I create a view with the names for both MainDepartment and SecondDepartment?

contactview
-----------
ContactID   ContactName     MainDepartmentName   SecondDepartmentName
-------------------------------------------------------------------
1           Fred Bloggs     Department 1     Department 2
2           Bob Smith       Department 2     Department 2

I tried CROSS JOIN but that did not work (1 entry  in contacts resulted in one row in the view, but 2 entries resulted in 8)