I have a table that has repair ticket information and another one
that has information about technicians. The repair ticket column
"technician" contains a number that references the ID in the
technician database: tecnicians REFERENCES people_alt(people_alt_id)
I query like this (see below) to combine the data into one
recordset. This works great if a technician has been assigned to the
ticket. But if a technician has not yet been assigned, it comes up
blanks. I understand why; it's the 'AND p.people_alt_id =
a.technician' part. But I'm not sure what to do about it. I query
to see if a technician has been assigned and do a different query
altogether if a technician has not been assigned. But then I'm
missing expected fields in the output.
Any advice on how to approach this is appreciated.
<CFQUERY NAME="getSelect" DATASOURCE="dpch">
SELECT
a.repair_ticket_id,a.date_added,a.fname,a.lname,a.department,a.fix_days,
a.time_of_day,
a.technician,a.technician_fname,a.technician_lname,a.status,a.severity,a
..description,
a.issue_type,a.platform,a.hardware_type,a.model,a.make,a.operating_syste
m,
p.location AS technician_location,p.email AS
technician_email,p.phone AS technician_phone
FROM repair_ticket a, people_alt p
WHERE a.repair_ticket_id = #id# AND p.people_alt_id =
a.technician
</CFQUERY>
thank you.
--
Daniel Kessler
College of Health and Human Performance
University of Maryland
Suite 2387 Valley Drive
College Park, MD 20742-2611
Phone: 301-405-2545
http://hhp.umd.edu
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255986
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
