Joining data from two tables (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

nettop -> Joining data from two tables (6/5/2006 14:53:11)

I've read through several examples of this but can't get my attempt at joining fields from two tables that have the same ID field to work (Ideally, I would only show the response for the most recent response only). Here are the specifics: I have a table called "issues" that I'm trying to join with a table called "responses" in the following manner to show: 
<thead>
<tr>
[color=#FF0000]<<this is a note and NOT in the code, the following two field are from the table 'issues'>>[/color]
<th ALIGN="LEFT"><b>date_submitted</b></th>
<th ALIGN="LEFT"><b>issue_id</b></th>
<th ALIGN="LEFT"><b>issue_name</b></th>
<th ALIGN="LEFT"><b>issue_desc</b></th>

[color=#FF0000]<<this is a note and NOT in the code, the following two field are from the table 'responses'>>[/color]

<th ALIGN="LEFT"><b>Response Date</b></th>
<th ALIGN="LEFT"><b>Response</b></th>
</tr>
</thead>
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<% end if %>
<%
fp_sQry="SELECT issues.date_submitted,issue_id,issue_name,issue_desc, responses.date_response,response FROM issues, responses WHERE issues.issue_id=responses.issue_id Order by issue_id Asc"
fp_sDefault="null"
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="issues"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="date_submitted"
fp_sMenuValue="date_submitted"
fp_sColTypes="&issue_id=3&issue_name=202&issue_desc=203&user_id=3&priority_id=3&status_id=3&version=202&approved=17&tested=17&assigned_to=3&assigned_to_orig=3&date_submitted=135&date_resolved=135&date_modified=135&modified_by=3&response_id=3&issue_id=3&response=203&user_id=3&priority_id=3&status_id=3&version=202&approved=17&tested=17&assigned_to=3&date_response=135&"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<%=FP_FieldVal(fp_rs,"date_submitted")%></td>
<td>
<%=FP_FieldVal(fp_rs,"issue_id")%></td>
<td>
<%=FP_FieldVal(fp_rs,"issue_name")%></td>
<td>
<%=FP_FieldVal(fp_rs,"issue_desc")%></td>
<td>
<%=FP_FieldVal(fp_rs,"date_response")%></td>
<td>
<%=FP_FieldVal(fp_rs,"response")%></td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
Thanks for your help, Tim



rdouglass -> RE: Joining data from two tables (6/5/2006 15:36:15)

quote:

fp_sQry="SELECT issues.date_submitted,issue_id,issue_name,issue_desc, responses.date_response,response FROM issues, responses WHERE issues.issue_id=responses.issue_id Order by issue_id Asc"


That looks wrong in the sense that you need some sort of JOIN statement in there like so:

fp_sQry="SELECT issues.date_submitted,issue_id,issue_name,issue_desc, responses.date_response,response FROM issues, responses LEFT JOIN responses ON responses.issue_id = issues.issue_id Order by issue_id Asc"

Just a quick guess but I suspect it's close.



yb2 -> RE: Joining data from two tables (6/5/2006 17:43:38)

you'll be better off using an INNER JOIN than a LEFT JOIN unless you have some specific need to retrieve all the records and ones without relations in the joining table.



rdouglass -> RE: Joining data from two tables (6/5/2006 20:45:38)

quote:

you'll be better off using an INNER JOIN than a LEFT JOIN


Just grabbed it from one of my current scripts and I seem to use more of them than about any. You may be correct depending on the application, but I guess that's just how I heard the question. [;)]



yb2 -> RE: Joining data from two tables (6/6/2006 4:08:18)

quote:

I seem to use more of them than about any


ooh, I'd have a look at those RD - you're letting it slip![8D][8|][:D]



nettop -> RE: Joining data from two tables (6/10/2006 20:38:38)

Here's what I've got and it still doesn't work: 

		<tr>
			<td width="100%" colspan="3"><b><font face="Arial">Issues Report for 
			CAO Weekly Meeting</font></b><table width="100%" border="1" cellspacing="0" bordercolor="#000080">
				<thead>
					<tr>
						<th ALIGN="LEFT"><b>date_submitted</b></th>
						<th ALIGN="LEFT"><b>issue_id</b></th>
						<th ALIGN="LEFT"><b>issue_name</b></th>
						<th ALIGN="LEFT"><b>issue_desc</b></th>
						<th ALIGN="LEFT"><b>Response Date</b></th>
						<th ALIGN="LEFT"><b>Response</b></th>
					</tr>
				</thead>
				<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<% end if %>
<%
fp_sQry="SELECT issues.date_submitted,issue_id,issue_name,issue_desc, responses.date_response,response FROM issues, responses INNER JOIN responses ON responses.issue_id = issues.issue_id Order by issue_id Asc"

fp_sDefault=""
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="issues"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="date_submitted"
fp_sMenuValue="date_submitted"
fp_sColTypes="&issue_id=3&issue_name=202&issue_desc=203&user_id=3&priority_id=3&status_id=3&version=202&approved=17&tested=17&assigned_to=3&assigned_to_orig=3&date_submitted=135&date_resolved=135&date_modified=135&modified_by=3&response_id=3&issue_id=3&response=203&user_id=3&priority_id=3&status_id=3&version=202&approved=17&tested=17&assigned_to=3&date_response=135&"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
						<td>
						<%=FP_FieldVal(fp_rs,"date_submitted")%></td>
						<td>
						<%=FP_FieldVal(fp_rs,"issue_id")%></td>
						<td>
						<%=FP_FieldVal(fp_rs,"issue_name")%></td>
						<td>
						<%=FP_FieldVal(fp_rs,"issue_desc")%></td>
						<td>
						<%=FP_FieldVal(fp_rs,"date_response")%></td>
						<td>
						<%=FP_FieldVal(fp_rs,"response")%></td>
					</tr>
					<!--#include file="../_fpclass/fpdbrgn2.inc"-->



BeTheBall -> RE: Joining data from two tables (6/10/2006 22:49:51)

Can you elaborate a bit on what doesn't work? Are you getting an error message? If so, what is it?



nettop -> RE: Joining data from two tables (6/11/2006 2:06:24)

Here's the error message:

Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.



yb2 -> RE: Joining data from two tables (6/11/2006 3:07:16)

This may not fix the error, but each of the columns metioned in the sql statement should have the table name prefixed, even when there isn't another column called that used elsewhere in the query. Any ambiguity should be removed. Including the Order By.



BeTheBall -> RE: Joining data from two tables (6/11/2006 11:42:15)

To see the true error, follow Spooky's instructions in the first post of this thread:

http://www.frontpagewebmaster.com/m-175524/tm.htm



nettop -> RE: Joining data from two tables (6/11/2006 14:27:46)

Database Results Wizard Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



BeTheBall -> RE: Joining data from two tables (6/11/2006 16:38:10)

Does this get you any closer?

fp_sQry="SELECT issues.date_submitted, issues.issue_id, issues.issue_name, issues.issue_desc, responses.date_response, responses.response FROM issues INNER JOIN responses ON responses.issue_id = issues.issue_id Order by issue_id Asc"

Note, yb2's post about prefixing each field name with the table name like you have done in issues.date_submitted is sound advice.



nettop -> RE: Joining data from two tables (6/11/2006 17:32:22)

Here's the error now:

Database Results Wizard Error
Description: [Microsoft][ODBC Microsoft Access Driver] The specified field 'issue_id' could refer to more than one table listed in the FROM clause of your SQL statement.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers



BeTheBall -> RE: Joining data from two tables (6/11/2006 17:43:40)

fp_sQry="SELECT issues.date_submitted, issues.issue_id, issues.issue_name, issues.issue_desc, responses.date_response, responses.response FROM issues INNER JOIN responses ON responses.issue_id = issues.issue_id Order by issues.issue_id Asc"



nettop -> RE: Joining data from two tables (6/11/2006 19:04:41)

You did it...PERFECTO...

Now, is it possible to show just the most recent response entry?

One idea would be to sort by each issue_id and then by each response_id (showing only the most recent, always, largest ID number). Your thoughts???

Thanks so much for your help, Tim



BeTheBall -> RE: Joining data from two tables (6/11/2006 20:27:42)

You need to use the Max function. This may work: 

fp_sQry="SELECT Max(responses.issue_id) AS fldID, issues.date_submitted, issues.issue_name, issues.issue_desc, responses.date_response, responses.response FROM issues INNER JOIN responses ON responses.issue_id = issues.issue_id GROUP BY (issues.date_submitted, issues.issue_name, issues.issue_desc, responses.date_response, responses.response) ORDER BY responses.issue_id ASC"



nettop -> RE: Joining data from two tables (6/11/2006 22:52:15)

Error as follows:

Database Results Wizard Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression '(issues.date_submitted, issues.issue_name, issues.issue_desc, responses.date_response, responses.response)'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



BeTheBall -> RE: Joining data from two tables (6/11/2006 22:58:10)

That was a poorly thought out answer. Sorry about that. This should be a little better: 

fp_sQry="SELECT responses.issue_id, issues.date_submitted, issues.issue_name, issues.issue_desc, responses.date_response, responses.response FROM issues INNER JOIN responses ON responses.issue_id = issues.issue_id WHERE responses.issue_id+responses.date_response IN (SELECT responses.issue_id+Max(responses.date_response) FROM responses GROUP BY responses.issue_id)"



nettop -> RE: Joining data from two tables (6/12/2006 0:36:12)

Duane: That did it...YOU ARE THE BEST!!!! Thanks so much.

Joining data from tables should be a much easier process. I hope that future versions of web authoring tools make it easier...

Until then, I really appreciate your insight and help...Take care, Tim



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125