Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004

    Unanswered: SQL INNER JOIN help

    I have a query where I need to get two names from a table. The names are on one project but have two seperate roles. In the previous db this want a problem because everything was in two tables. This one has 3 tables to relate this info

    heres my sql

    SELECT budgetAmount AS TrainingBudget, tp_project.projId AS ProjectNumber, courseDescription AS ProjectDescription, tp_proj_contact.firstName + ' ' + tp_proj_contact.lastName AS TrainingOwner, sponsorB.firstName + ' ' + sponsorB.lastName AS sponsorName
    FROM tp_project
    INNER JOIN tp_proj_contact ON tp_proj_contact.projID = tp_project.projID AND contactTypeId = 33
    INNER JOIN tp_users ON hrid = contactHRID
    LEFT OUTER JOIN tp_proj_contact AS sponsorA ON sponsorA.projID = tp_project.projID AND sponsorA.contactTypeId = 20
    LEFT OUTER JOIN tp_users AS sponsorB ON sponsorB.hrid = sponsorA.contactHRID
    WHERE tp_project.projID = 633

    The differences are the contactTypeID one is 33 the other is 20, the error I get is

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'firstName'.
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'lastName'.

    Is it that I can't join the same table twice in one query? If so what alternatives do I have?

  2. #2
    Join Date
    Mar 2004
    Reston, Virginia
    Khary ...

    Looking at your SQL statement quickly, two thoughts come to mind. First, and simplest: Is your system case sensitive? If so, I think I saw "ID" and "Id" mixed.

    Second, I think you can do a "self-join" (joining the same table twice) by using an alias on the second attachment (you already are using AS in some of your field names).

    Of course, mine is not expert advice, as you will see later in my posting. I have just started playing around with INNER JOINs. Microsoft documentation I have read discouraged OUTTER JOINs (LEFT and RIGHT) because they can cause problems by delivering too many records.

  3. #3
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    If you use a table alias, then the table columns can only be referenced with the alias.


    Select tableA.columnA
    from tableA ta

    This query will not work, instead the alias 'ta' must be used to reference 'columnA'.
    Last edited by r123456; 03-02-04 at 10:01.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Do both the tp_proj_contact and tp_users tables have columns named firstName and lastName?


Posting Permissions

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