It's easy
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
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)