SQL View question

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)

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)

Select
 contacts.ContactID,
 contacts.ContactName,
 MainDept.DepartmentName as MainDepartmentName,
 SecondDept.DepartmentName  as SecondDepartmentName
From
 contacts
 inner join departments MainDept on contacts.MainDepartment=MainDept.DepartmentID
 inner join departments SecondDept on contacts.SecondDepartment=SecondDept.DepartmentID


If this was a real example you could consider changing your table design to suppport N departments.

Thanks. It works fine.

Don't know why I didn't figure it out (I have done inner joins before - mind just went blank). Before CROSS JOIN, I used

INNER JOIN departments ON departments.DepartmentID = contacts.MainDepartment AND departments.DepartmentID = contacts.SecondDepartment

Which is obviously wrong (unless I wanted a view where both MainDepartment and SecondDepartment are the same)