Tech Off Thread

4 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

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)

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.