Solved

sql 2005, inner join

Posted on 2007-11-18
5
252 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now