• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

how do i join three tables

Hi,
I have a query that has two joined tables but now I need to join another table with the ability to delete files.

Here is my code so far:

How do I join tblCandidatePortfolioProjectLinks with column names of (link_id, req_part_id, text_title, description)

Thanks!
<cfquery name="qcheck" datasource="#APPLICATION.datasource#">
SELECT f.[file_id], f.extension, v.candidate_id 
FROM tblCandidatePortfolioProjectFiles f
INNER JOIN vwCandidatePortfolioProjects v
      ON f.req_part_id = v.req_part_id
      AND v.submitted IS NULL
WHERE v.constitid = #SESSION.current_user.constitid# AND f.[file_id] = <cfqueryparam value="#Form.id#" cfsqltype="cf_sql_integer">
</cfquery>

<cfif qcheck.recordcount eq 1>
	<cfset thisDir = "#APPLICATION.upload_dir##qcheck.candidate_id#">
    <cffile action="delete" file="\#thisDir#\worksample_#qcheck.file_id#.#qcheck.extension#">
    <cfif FileExists("\#thisDir#\worksample_T#qcheck.file_id#.#qcheck.extension#")>
    
    	<cffile action="delete" file="\#thisDir#\worksample_T#qcheck.file_id#.#qcheck.extension#">
    </cfif>
    <cfquery name="qdelete_files" datasource="#APPLICATION.datasource#">
    DELETE FROM tblCandidatePortfolioProjectFiles where file_id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
    </cfquery>
  

<cfelse>
	Can not delete file
</cfif>

Open in new window

0
smfmetro10
Asked:
smfmetro10
1 Solution
 
gdemariaCommented:
> I have a query that has two joined tables but now I need to join another table with the ability to delete files.

This doesn't really make sense... can you rephrase this please?

Keep in mind that you know your application, we don't know it at all... you have to be specific about what you want to do and to what..

0
 
billfusionCommented:
What column is common between your current two tables and tblCandidatePortfolioProjectLinks?  Typically, you can simply add the new join statement to the current SQL to have it work.  Here is an Microsoft example;

 
SELECT a.au_lname, a.au_fname, t.title
FROM authors a INNER JOIN titleauthor ta
   ON a.au_id = ta.au_id JOIN titles t
   ON ta.title_id = t.title_id
WHERE t.type = 'trad_cook'
ORDER BY t.title ASC

Open in new window

0
 
smfmetro10Author Commented:
great thanks!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now