• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

sql 2005, inner join

the attached code snippet works fine as is.

what I want to understand, relating to this code, how do I link 2 sql tables as follows:

[CallBackItem_ID] is an integer field, refering to a Primary Field called:  [CallBackItem_ID] in a table called: CallBackItems

What I am looking to have returned is not the [CallBackItem_ID] number as this is a reference to another table, but from that other table, CallBackItems   the CallBackItemDesc field (which has an text description, which is more meaningful to user than a number).  CallBackItems table is used to propagate a drop down list.

What do I need to add to this code in order for this to happen please?

Time and efforts with the enqury are much apprieated.
<asp:SqlDataSource ID="SqlDataSource_ProspectCallbackHistory" runat="server" ConnectionString="<%$ ConnectionStrings:FORTUNEConnectionString %>"
            SelectCommand="SELECT [CallBackItem_ID], [DateTimeStamp], [DataEntryUser] FROM [CallBackProspectCallHistory] WHERE ([MasterAccount_ID] = @MasterAccount_ID) ORDER BY [DateTimeStamp] DESC">
                <asp:SessionParameter Name="MasterAccount_ID" SessionField="sessionMasterAccount"
                    Type="Int32" />

Open in new window

3 Solutions
SELECT CallBackItemDesc , [DateTimeStamp], [DataEntryUser]
FROM [CallBackProspectCallHistory] inner join CallBackItems On CallBackProspectCallHistory.CallBackItem_ID = CallBackItems.CallBackItem_ID
amillyardAuthor Commented:

I have made these changes as indicated above -- but I am getting an error (when testing for a result) as follows:

There was an error executing the query.  Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct.

Ambiguous column name 'DateTimeStamp'.
Alias the tables and prefix the column names with the table alias:

SELECT I.CallBackItemDesc , I.[DateTimeStamp], I.[DataEntryUser]
FROM [CallBackProspectCallHistory]  H
inner join CallBackItems I
On H.CallBackItem_ID = I.CallBackItem_ID
Hi amillyard

Try this, the tables are aliased (p & i) and the columns that are bing queried are selected from one of the those tables (p or i).  this query does not return any columns from [CallBackItems] to get these just add them to the select list and prefix them with "i." (no quotes)
	, p.[DateTimeStamp]
	, p.[DataEntryUser]
	[CallBackProspectCallHistory] p
	JOIN [CallBackItems] i ON p.[CallBackItem_ID] = i.[CallBackItem_ID]
	p.[MasterAccount_ID] = @MasterAccount_ID
	p.[DateTimeStamp] DESC

Open in new window

amillyardAuthor Commented:

this was the only response that worked -- many thanks.  also added columns via the 'i' no problem as well :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now