|
| |
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
 |
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
< Message edited by rdouglass -- 6/5/2006 15:36:43 >
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
yb2
Posts: 653
Joined: 1/30/2006
Status: offline
|
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.
_____________________________
it is natural for people not to see one's own faults, and to exaggerate other people's faults and failings.
Currently listening to: L'Enfer Des Formes by Stereolab
| |
|
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
|
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"-->
< Message edited by BeTheBall -- 6/10/2006 22:47:31 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
|
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
Posts: 653
Joined: 1/30/2006
Status: offline
|
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.
_____________________________
it is natural for people not to see one's own faults, and to exaggerate other people's faults and failings.
Currently listening to: L'Enfer Des Formes by Stereolab
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
|
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
< Message edited by nettop -- 6/11/2006 19:39:50 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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)"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
nettop
Posts: 212
Joined: 2/9/2004
Status: offline
|
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
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
