Using T1 and T2 References in a Select Statement

Question

Post reply

Using T1 and T2 References in a Select Statement

john monroe

Old Hand

Points: 365
More actions
November 7, 2007 at 9:04 pm

#183806

I am trying to reference 2 columns that have references to userID numbers.
The colums are secondarycontactID and OwnerID. I have to look up the values in another table called userinfo.
I have defined the query as below. How can I add a second ON statement to map T1.secondaryContactID to T2.tp_Login.
SELECT T1.FullUrl, T3.size AS Size, T1.SecondaryContactID, T2.tp_Login AS PSCO
FROM Sites AS T1 INNER JOIN
UserInfo AS T2 ON T1.OwnerID = T2.tp_ID CROSS JOIN
sysfiles AS T3
WHERE (T3.fileid = 1)
This query works but I need to be able to take the T1.sites.secondaryContactID and look up the value in T2.UserInfo.tp_Login

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

AND

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john monroe Old Hand Points: 365 More actions · Answer 2

2 Clarifications please.

I am assuming I put the and here.

SELECT T1.FullUrl, T3.size AS Size, T1.SecondaryContactID, T4.tp_Login, T2.tp_Login AS PSCO, T1.OwnerID

FROM Sites AS T1 INNER JOIN

UserInfo AS T2 ON T1.OwnerID = T2.tp_ID

ANDT1.SecondaryContactID = T2.tp_ID CROSS JOIN

sysfiles AS T3

WHERE (T3.fileid = 1)

But when I run the query it returns nothing now. Also I think I have to assign a T value to the tp_login that is mapped to the T1.secondaryContactID. But I am not sure if I should use T4 or have I already declared it and I am just being dense. Thanks for your help in advance. I am working hard to understand and improve my T-SQL coding Skills.

john monroe Old Hand Points: 365 More actions · Answer 3

Thanks. Your reply got me thinking about it differently.

The answer turned out to be:

Not an AND but adding INNER JOIN to the Second LINE. LOL.

SELECT T1.FullUrl, T4.size AS Size, T1.SecondaryContactID, T5.tp_Login, T2.tp_Login AS PSCO, T1.OwnerID

FROM Sites AS T1 INNER JOIN

UserInfo AS T2 ON T1.OwnerID = T2.tp_ID INNER JOIN

UserInfo AS T5 ON T1.SecondaryContactID = T5.tp_ID CROSS JOIN

sysfiles AS T4

WHERE (T4.fileid = 1)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Glad it worked out, John... I misunderstood thinking that you wanted to add a additional condition to an existing join. And, thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)