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
<mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
"What is the distance between the eyes and the soul?"
Fortune Cookie
-------------------------------------------------------------
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
-------------------------------------------------------------
