Thank you Jon, I'll admit I was so elated when my 3-select + 2-join concoction worked that I stopped looking further. Thank you too for the referral to w3schools. They are one of my sources for help when at first I don't succeed.
I just dropped your query re-write into my SQL Query Analyzer. Unfortunately it is only producing a result set of two records. The correct result set should be 18 records. 12 Nursalim 1 17 Yemenidjian 1 What are missing here are the 16 jobs where the vendorID should be <null> My solution thought process was to build the parts and then combine them. My first sub-query select produced the job list from the job_tbl. The second sub-query select produces the result set of jobs assigned to a particular vendor. And then my primary select gave me the columns I needed from the join of the two sub-sets for a final result set. This app's database will likely remain small. The job list will average around 20 over time, and the number of vendors assigned to an individual job will rarely exceed 100. You are absolutely right that any query should be optimized to greatest efficiency. Sometimes though I am guilty of following that old adage: If it ain't broke, don't fix it. When I finally got it to work I was so relieved to just leave it alone. Thanks again, Dan -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of JON SMITH Sent: Sunday, August 26, 2007 8:18 PM To: [email protected] Subject: RE: [ACFUG Discuss] SQL JOIN problem -SOLVED- Dan, Your SQL may work, but it's redundant. You have 3 selects and 2 joins when only 1 select and 1 join is needed. If this is one of many queries going into a larger ColdFusion program, you will definitely want to optimize it. Try the following: SELECT j.GGCjobID, j.GGCjobName, v.vendorID FROM GGCjob_tbl j LEFT OUTER JOIN vendorJobAssignment_tbl v ON j.GGCjobID = v.GGCjobID WHERE j.GGCjobActive > 0 and v.vendorID = '1' Also, look into gaining an understanding of the different joins (inner, left outer, right outer, cross) as you will be using joins ALOT. W3C is a good place to start. http://www.w3schools.com/sql/sql_join.asp Best of luck. >>> <[EMAIL PROTECTED]> 08/26/07 5:38 PM >>> Thanks again Dean...and the GOOD NEWS is I found THE SOLUTION. First off, in my example SQL statements AND sample table data, I abbreviated some of the table names etc. for clarity. I say this only because in the SQL statement here following you'll see some minor differences in the naming of things. Never-the-less, this query produces EXACTLY what I need: SELECT tbl_1.GGCjobID, tbl_1.GGCjobName, tbl_2.vendorID FROM ( SELECT GGCjobID, GGCjobName FROM GGCjob_tbl j WHERE j.GGCjobActive > 0 )tbl_1 LEFT OUTER JOIN ( SELECT vja.GGCjobID, j.GGCjobName, vja.vendorID FROM vendorJobAssignment_tbl vja JOIN GGCjob_tbl j ON vja.GGCjobID = j.GGCjobID WHERE vja.vendorID = '1' )tbl_2 ON tbl_1.GGCjobID = tbl_2.GGCjobID and the results table: 1 Andre NULL 2 Beer NULL 3 Berezovsky NULL 4 Brooks NULL 5 Broillet NULL 6 Dubchansky NULL 7 Fontana NULL 8 Johnson NULL 9 Kort NULL 10 Liberman NULL 11 Maron NULL 12 Nursalim 1 13 Schwab NULL 14 Shekhter NULL 15 Wallace -Jason NULL 16 Wallace -Steve NULL 17 Yemenidjian 1 18 Zaks NULL Thanks again ACFUG for being there. Even though (my perseverance) found the solution, just having you guys there to bounce the problem off helped. Really. Typing out the problem, striving to make my own internal confusion clear to others, helped me see the ultimate course to WHOOPEE. Dan Kaufman -----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 ------------------------------------------------------------- ----------------------------------------- NOTICE: This communication is intended only for the person or entity to whom it is addressed and may contain confidential, proprietary, and/or privileged material. Unless you are the intended addressee, any review, reliance, dissemination, distribution, copying or use whatsoever of this communication is strictly prohibited. If you received this in error, please reply immediately and delete the material from all computers. Email sent through the Internet is not secure. Do not use email to send us confidential information such as credit card numbers, PIN numbers, passwords, Social Security Numbers, Account numbers, or other important and confidential information. ------------------------------------------------------------- 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 -------------------------------------------------------------
