?
Solved

sql 2005, inner join

Posted on 2007-11-18
5
Medium Priority
?
258 Views
Last Modified: 2013-12-17
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">
            <SelectParameters>
                <asp:SessionParameter Name="MasterAccount_ID" SessionField="sessionMasterAccount"
                    Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>

Open in new window

0
Comment
Question by:amillyard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 200 total points
ID: 20307911
...
SELECT CallBackItemDesc , [DateTimeStamp], [DataEntryUser]
FROM [CallBackProspectCallHistory] inner join CallBackItems On CallBackProspectCallHistory.CallBackItem_ID = CallBackItems.CallBackItem_ID
WHERE ...
0
 

Author Comment

by:amillyard
ID: 20308017
MikeToole:

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'.
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 200 total points
ID: 20308277
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
WHERE
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 1600 total points
ID: 20308320
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)
SELECT
	p.[CallBackItem_ID]
	, p.[DateTimeStamp]
	, p.[DataEntryUser]
FROM
	[CallBackProspectCallHistory] p
	JOIN [CallBackItems] i ON p.[CallBackItem_ID] = i.[CallBackItem_ID]
WHERE
	p.[MasterAccount_ID] = @MasterAccount_ID
ORDER BY
	p.[DateTimeStamp] DESC

Open in new window

0
 

Author Comment

by:amillyard
ID: 20308583
mcmonap:

this was the only response that worked -- many thanks.  also added columns via the 'i' no problem as well :-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question