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