Two columns referencing same field in inner join

Question

Post reply

Two columns referencing same field in inner join

andy behrendt

SSC Enthusiast

Points: 177
More actions
May 24, 2006 at 12:04 am

#172578

Hi All,
I have a table in SQL 2000 that contains two fields which store an employee id field. One is called fldRaisedBy and the other is called fldIdentifiedBy. The employee id is a link to another sql database that contains all of the employee's details. I need to be able to display this information in an asp page, however I can not work out how to retrieve the linked data twice in one select statement. My stored procedure currently looks like this:
CREATE PROCEDURE getCar
(
@varNumber int
)
AS
Select fldJobNumber,
fldIdentifiedBy,
fldLastEdit,
fldCauseCode,
PWIN171.dbo.FE.FE_NAME,
PWIN171.dbo.FE.FE_DEPT,
PWIN171.dbo.QM.QM_TITLE
From tblHeaders
Inner Join PWIN171.dbo.FE On LTrim(RTrim(Cast(tblHeaders.fldRaisedBy as char))) = LTrim(RTrim(PWIN171.dbo.FE.FE_CODE))
Inner Join PWIN171.dbo.FE On LTrim(RTrim(Cast(tblHeaders.fldIdentifiedBy as char))) = LTrim(RTrim(PWIN171.dbo.FE.FE_CODE))
Inner Join PWIN171.dbo.QM On LTrim(RTrim(Cast(tblHeaders.fldJobNumber as char))) = LTrim(RTrim(PWIN171.dbo.QM.QM_JOB_NUM))
Where tblHeaders.fldID = @varNumber
Order By fldJobNumber
GO
In effect, I will need to display Joe Smith as fldRaisedBy and Fred Bloggs as fldIdentifiedBy. Can anyone help me with figuring out how to achieve this?
Cheers
ab

Viewing 3 posts - 1 through 2 (of 2 total)

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

andy behrendt SSC Enthusiast Points: 177 More actions · Answer 1

The following answer was supplied to me on another forum (thanks Bill W!), and might prove useful to another user:

Personally, I always alias both (or all 3 or 4 or ... ) to ensure that it is clear what the intent is.

Why do you cast a field to char only to then trim it??? What kind of field was it originally? If it was numeric, casting it to VARCHAR will do the same as casting to char and then trimming.

Partial modification of your query shown here. I wasn't clear which field was coming from which JOIN of the same table, so I made up part of this on my own:

Select fldJobNumber,

fldIdentifiedBy,

fldLastEdit,

fldCauseCode,

FERaised.FE_NAME AS RaisedByName,

FEIdentified.FE_NAME AS IdentifiedByName

From tblHeaders

Inner Join PWIN171.dbo.FE AS FERaised

On LTrim(RTrim(Cast(tblHeaders.fldRaisedBy as char))) = LTrim(RTrim(FERaised.FE_CODE))

Inner Join PWIN171.dbo.FE AS FEIdentified

On LTrim(RTrim(Cast(tblHeaders.fldIdentifiedBy as char))) = LTrim(RTrim(FEIdentified.FE_CODE))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Unrelated to your question, but something you should know.

Inner Join PWIN171.dbo.FE AS FERaised

On LTrim(RTrim(Cast(tblHeaders.fldRaisedBy as char))) = LTrim(RTrim(FERaised.FE_CODE))

With the functions around the fields in the join clause, you are preventing SQL from using any indexes that might exist on tblHeaders.fldRaisedBy

Also if you case a field to char (or varchar) you should specify the length of the data type, otherwise you get the deault of 30

Why not just join ON tblHeaders.fldRaisedBy = FERaised.FE_Code ?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass