how do i join three tables

Posted on 2011-10-25
Last Modified: 2012-05-12
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)

<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="" cfsqltype="cf_sql_integer">

<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#">
    <cfquery name="qdelete_files" datasource="#APPLICATION.datasource#">
    DELETE FROM tblCandidatePortfolioProjectFiles where file_id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">

	Can not delete file

Open in new window

Question by:smfmetro10
    LVL 39

    Expert Comment

    > 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..

    LVL 6

    Accepted Solution

    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


    Author Closing Comment

    great thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
    This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now