Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Transact SQL question - Joining tables

    Consider the following:
    1) tblCase (ID,Issue,Msg,EngineerID,ArchitectID)
    each case is assigned to a team consisting of an engineer and architect.
    The Engineers and Architects info is saved in the follwoing table:

    2) tblStaff (ID,Name,Title)



    Problem:
    I need to write a T-SQL statment to select each "Issue" and return the engineer and architect's name (not ID).
    If there was only one ID field as a foriegn key in tblCase there would be no problem in joining the two tables, but since both the EngineerID and ArchitectID are foriegn keys form tblStaff I can't figure out how to join the two tables!
    Can anybody help me?
    Last edited by Sia; 06-11-03 at 18:23.
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    We have several issues like that here. Here is some code that will do that by aliasing your Staff Table into two tables.

    SELECT C.ID, C.Issue, C.Msg, E.Name, A.Name
    FROM tblCase C JOIN tblStaff E ON (C.EngineerID=E.ID)
    JOIN tblStaff A ON (C.ArchitectID=A.ID)

    This works for a provider Table that I have that links twice to an operator table, hope it helps you out.

    Brent

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks, it solved my problem
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Also just in case refer to books online for syntax and other information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •