Paul,
You need a joined query between get_Report_Details and get_extension, so
that you have one query.
At the minute you're only getting the doc type for the first report in
get_Report_Details, hence both reports wind up with a pdf extension.
SELECT Report_Details.doc_path, Report_Details.Report_Name,
Doc_Type.Doc_Type
FROM Report_Details
LEFT JOIN Phases ON Reports_Details.Phases LIKE '%'+Phases.Phase_ID+'%'
LEFT JOIN Doc_Type ON Report_Details.doc_type_id =
Doc_Type.Doc_Type_ID
WHERE ReportDetails.Group_ID = '#url.group_ID#' AND Phases.Phase =
'#url.phase#'
Ermmm... not keen on that "Phase LIKE ....." bit in the first join, but it
should work.
If a report only has one Phase then you should change the
"Reports_Details.Phases LIKE '%'+Phases.Phase_ID+'%' "
to be
"Reports_Details.Phases =Phases.Phase_ID"
If you have more than one phase in the Phases field in Report_Details then
really that should be taken out of there into another table (if you can), so
that you have Report_ID and Phase_ID as columns in a seperate link table.
The two columns together should form a composite primary key for that table.
Hope that helps
Regards
Stephen
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, September 18, 2003 3:56 PM
Subject: [ cf-dev ] I just can't do it capt'n
>
> I'm sure that this is a database design problem but as I have little
> control over that how can I solve this problem ....?
>
> The following code produces a link to a document ......
> The link is made up like this
> /reports/<year>/<phase>/<path>/<school>/<school>.<extension>
>
> phase = primary
> secondary
>
> path = path in database (eg ks1va)
>
> School = School DFEE No
>
> Extension = .pdf .doc.xls etc
>
> So the problem is this. The database has a table called Report details.
> This table has an entry thus ...
>
> Report_ID Group_ID ReportName Doc_Type_ID Doc_Path Phases
> 8 6 abc 1 fndation 24
> 9 6 def 3 barchart 2
>
> Using the code below all is cool and both reports show as links.
>
> eg.
> /reports/2003/primary/fndation/2000/2000/abc.pdf
> /reports/2003/primary/barchart/2000/2000/def.pdf
>
> However both links have the extension .pdf where the first should have
.pdf
> and the second .xls
> I can see that the query looping is causing the problem but how can I fix
> it .....?
>
>
> Regards - Paul (I hope I have explained this one ok)
>
>
> <cfif isdefined ("url.group_ID")>
> <CFQUERY NAME="get_Phase_ID"
> DATASOURCE="WebUserDSN">
> SELECT *
> FROM Phases
> WHERE Phase = '#url.phase#'
> </CFQUERY>
>
> <CFQUERY NAME="get_Report_Details"
> DATASOURCE="WebUserDSN">
> SELECT *
> FROM Report_Details
> WHERE Group_ID = '#url.group_ID#' and Phases like ('%
> #get_Phase_Id.phase_ID#%')
> </CFQUERY>
>
> <CFQUERY NAME="get_extension"
> DATASOURCE="WebUserDSN">
> SELECT *
> FROM Doc_Type
> WHERE Doc_Type_ID = '#get_report_details.doc_type_ID#'
> </CFQUERY>
>
>
> <cfif get_report_details.recordcount gt 1>
> <tr>
> <td valign="top" width="580" align="center" style
> ="font-family: Arial; font-size: 10pt">
> <br><!--mstheme--><font face="arial, Arial, Helvetica" size
> ="2">
> Please select a report group from the list below<p></p>
> <cfoutput query="get_report_details">
> <a href
>
="reports/#url.year#/#url.phase#/#doc_path#/#url.school#/#url.school##get_ex
tension.doc_type#">
> #Report_Name#</a><br>
> </cfoutput>
>
>
>
>
>
****************************************************************************
*********************
> The information contained within this e-mail (and any attachment) sent by
Birmingham City Council is confidential and may be legally privileged. It is
intended only for the named recipient or entity to whom it is addressed. If
you are not the intended recipient please accept our apologies and notify
the sender immediately, or telephone +(44) 121 303 6666. Unauthorised
access, use, disclosure, storage or copying is not permitted and may be
unlawful. Any e-mail including its content may be monitored and used by
Birmingham City Council for reasons of security and for monitoring internal
compliance with the office policy on staff use. E-mail blocking software may
also be used. Any views or opinions presented are solely those of the
originator and do not necessarily represent those of Birmingham City
Council. We cannot guarantee that this message or any attachment is virus
free or has not been intercepted and amended.
>
>
****************************************************************************
*********************
>
>
> --
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> For human help, e-mail: [EMAIL PROTECTED]
>
>
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
