Thanks Dean, however this (your query below) is (also) only giving me two records. The example tables in my posting with "shortened" for clarity's sake, in my current test data sets I should be getting 20 jobs from the job_tbl. And for vendorID = 1 there are only two records in the vja_tbl. Your suggestion is only giving me the two records, not the full set of 20 with a vendorID of 1 for two of them.
Dan -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dean H. Saxe Sent: Sunday, August 26, 2007 1:09 PM To: [email protected] Subject: Re: [ACFUG Discuss] SQL JOIN problem You need an inner join, if I understand correctly. Outer joins are only if you expect not to have a record in the left/right table to match the other table's record. SELECT * from job_tbl j, vja_tbl v WHERE j.jobID = v.jobID AND v.vendor_id = 1 -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "Great spirits have often encountered violent opposition from weak minds." --Einstein On Aug 26, 2007, at 3:38 PM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> wrote: > 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=gin.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 ------------------------------------------------------------- ------------------------------------------------------------- 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 -------------------------------------------------------------
