?
Solved

sql 2005, inner join

Posted on 2007-11-18
5
Medium Priority
?
260 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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