FK is showing PK ID instead of 'Name'

I have 2 tables, OT_ClaimantDetails and OT_OTDetails. PK in OT_OTDetails is OTDetailsID which is also FK in OT_ClaimantDetails. I am trying to run a select query to display certain data but instead of displaying the Name of the OT it displays the OTDetails ID.

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc
FROM OT_ClaimantDetails, OT_OTDetails
WHERE OT_ClaimantDetails.ClaimantDetailsOTDetailsID=OT_OTDetails.OTDetailsID

How should I change my query?

Is Name a column in one of the tables? If so, which one? If not, then where does it reside?

|||No, 'Name' is the users name I'm refering to. So basically instead of showing me 'ID 5' it should show me users number 5, for example, jack. In the OT_OTdetails table I have OTDetailsID and OTName. THe OTName is the 'Name' I would like to see instead of the OTDetailsID.|||

Unless there's something I'm missing you should be able to just add it to the select list.

Code Snippet

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc, OT_OTDetails.OTName

|||tried it, doesn't work. What I have done, I have changed the FK (used to be OT_ClaimantDetails.OTDetailsID) to OT_ClaimantDetails.ClaimantDetailsOTDetailsID, because I received a "Ambiguous column name" error in my browser. This solved the problem to display my results again, but now I see OTDetailsID instead of the OTName.|||

Can you post your DDL?

I don't see where you're referencing any OT_OTDetails columns in your select.

This doesn't make sense.

|||

I'm new at this, what do you mean by "post DDL"?

Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?|||

DDL = Data Definition Language....your table definitions.

>>Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?

No.

But you are joining the two tables together on the OTDetailsID (in your where clause).

So columns from the OT_OTDetail should be available.

When you put OTName in the select list, do you qualify it with OT_OTDetails. (not OT_ClaimantDetails)?

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

|||Standby guys, I'm going to try and get the DDL posted, maybe this will help you to help me.|||

I'm using SQL 2005.

Column OT_ClaimantDetails

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_ClaimantDetails] Script Date: 05/31/2007 17:07:41 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE [dbo].[OT_ClaimantDetails](

[ClaimantDetailsID] [int] IDENTITY(1,1)NOTNULL,

[ReferanceDIMS] [varchar](50)NULL,

[ReferanceClient] [varchar](50)NULL,

[DateReceived] [datetime] NULL,

[DateEntered] [datetime] NULL,

[DateContactClaimant] [datetime] NULL,

[DateContactClient] [datetime] NULL,

[DateAppointm] [datetime] NULL,

[DateComplExpect] [datetime] NULL,

[DateCompleteActual] [datetime] NULL,

[DateCompleteCalc] [nchar](10)NULL,

[ReportOT] [varchar](250)NULL,

[ReportClient] [varchar](250)NULL,

[ClaimantFactID] [int] NULL,

[CompanyDetailsID] [int] NULL,

[ClaimantDetailsOTDetailsID] [int] NULL,

[BenefitsID] [int] NULL,

[DiagnosisID] [int] NULL,

[EvaluationID] [int] NULL,

[OccupationalID] [int] NULL,

[RecommendationID] [int] NULL,

CONSTRAINT [PK_OT_ClaimantDetails] PRIMARYKEYCLUSTERED

(

[ClaimantDetailsID] ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits] FOREIGNKEY([BenefitsID])

REFERENCES [dbo].[OT_Benefits] ([BenefitsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact] FOREIGNKEY([ClaimantFactID])

REFERENCES [dbo].[OT_ClaimantFact] ([ClaimantFactID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails] FOREIGNKEY([CompanyDetailsID])

REFERENCES [dbo].[OT_CompanyDetails] ([CompanyDetailsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis] FOREIGNKEY([DiagnosisID])

REFERENCES [dbo].[OT_Diagnosis] ([DiagnosisID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation] FOREIGNKEY([EvaluationID])

REFERENCES [dbo].[OT_Evaluation] ([EvaluationID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational] FOREIGNKEY([OccupationalID])

REFERENCES [dbo].[OT_Occupational] ([OccupationalID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails] FOREIGNKEY([ClaimantDetailsOTDetailsID])

REFERENCES [dbo].[OT_OTDetails] ([OTDetailsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation] FOREIGNKEY([RecommendationID])

REFERENCES [dbo].[OT_Recommendation] ([RecommendationID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation]

--

COLUMN OT_OTDETAILS

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_OTDetails] Script Date: 05/31/2007 17:09:58 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE [dbo].[OT_OTDetails](

[OTDetailsID] [int] IDENTITY(1,1)NOTNULL,

[OTName] [varchar](50)NULL,

[OTLastName] [varchar](50)NULL,

[Tel] [nchar](10)NULL,

[Fax] [nchar](10)NULL,

[Cell] [nchar](10)NULL,

[Email] [varchar](50)NULL,

[City] [varchar](50)NULL,

CONSTRAINT [PK_OT_OTFact] PRIMARYKEYCLUSTERED

(

[OTDetailsID] ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

Let me know if this helps..

THX

|||

Thanks Jackow, that helped better understand your situation.

From you original description, I think this is closer to your objective:

Code Snippet

SELECT
d.OTName
c.ClaimantFactID,
c.ClaimantDetailsOTDetailsID,
c.ReferanceDIMS,
c.DateReceived,
c.DateEntered,
c.DateCompleteCalc
FROM OT_ClaimantDetails c
JOIN OT_OTDetails d
ON c.ClaimantDetailsOTDetailsID = d.OTDetailsID

|||

Thanks jackow. Very helpful.

Exactly Arnie...looks like the original concept should be working.

|||

HI Guys,

Sorry for my delayed response. Thanks for the help. We will chat soon!!