Try this. SELECT vja.jobID, j. jobName, vja.vendorID FROM job_tbl j LEFT JOIN vja_tbl vja ON j.jobID = vja.jobID WHERE vja.vendorID = '1' or vja.VendorID is null
Jay x8453 <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 08/26/2007 03:38 PM Please respond to [email protected] To <[email protected]> cc Subject [ACFUG Discuss] SQL JOIN problem Hello ALL, My problem is not strictly speaking a ColdFusion issue, however I am using it in a CF application AND I know there?re a few SQL geniuses out there. I?m hoping one of those geniuses can help me. I have two tables: job_tbl and vja_tbl job_tbl contains: jobID, jobName vja_tbl contains: jobID, vendorID vja_tbl is used to Assign Vendors to Jobs. I need a result set that contains: - all the jobs (all the records of the job_tbl) (all the records of the first table) - with only the vendorID of a specific/selected Vendor (only the redords of vja_tbl WHERE vja.vendorID = ?1?, for example) (only the records of the 2nd table WHERE?) I believe what I need is a LEFT OUTER JOIN, at least my description ?appears? to meet the definition of an OUTER JOIN. Example data: job_tbl jobID jobName 1 Andre 2 Beer 3 Beresovsky 4 Brooks 5 Fontana and so on? vja_tbl jobID vendorID 1 1 1 15 2 37 3 1 4 16 5 7 and so on? I will pass a variable, form.vendorID, to the query for a selected Vendor. I need a result set that gives me ALL the jobs (5 in this example) with the vendorID for only the jobs that this Vendor is assigned to. Desired result set for: vendorID = 1 jobID jobIName vendorID 1 Andre 1 2 Beer <null> 3 Beresovsky 1 4 Brooks <null> 5 Fontana <null> Example LEFT JOIN of (one of my attempts) SELECT vja.jobID, j. jobName, vja.vendorID FROM job_tbl j LEFT JOIN vja_tbl vja ON j.jobID = vja.jobID WHERE vja.vendorID = '1' However this only produces 2 records: jobID jobIName vendorID 6 Andre 1 7 Beresovsky 1 I have GOOGLED myself crazy looking for insight/education into how to do this, but (from my flawed point of view) every example I?ve found ?appears? to suggest a workable solution and yet I can?t get the full result set I need. It is import to the next step in the application that I have a full result set of ALL jobID?s with only a vendorID value equal to the selected vendor. All other records must contain <null>. The full-result set is presented to the user as a ?checkbox form? with checked jobNames for only those jobs to which the vendor is assigned to. The user can then check or un-check the jobNames and this ?updated? set is re-INSERTed to the the vja_tbl. I hope my explanation and examples clearly express what I need to do?I?m STUCK though on how to get there from here. Thanks for your consideration, Dan Kaufman [EMAIL PROTECTED] "What is the distance between the eyes and the soul?" Fortune Cookie ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
