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