Tech Off Thread

4 posts

SQL View question

Back to Forum: Tech Off
  • User profile image
    sbc

    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)

  • User profile image
    balexandre

    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)

  • User profile image
    Simo

    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.

  • User profile image
    sbc

    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)

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.