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
Solved

sql 2005, inner join

Posted on 2007-11-18
5
253 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
5 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 50 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 50 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 400 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

837 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