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?

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.

If you use a table alias, then the table columns can only be referenced with the alias.

Example:

Select tableA.columnA
from tableA ta

This query will not work, instead the alias 'ta' must be used to reference 'columnA'.

Do both the tp_proj_contact and tp_users tables have columns named firstName and lastName?

-PatP