Not sure if I understood your problem, but have you tried combining your ON clause with a WHERE clause with an OR condition:
Unfortunately, that wouldn't do it. the "client type" would be changing based on the client. I see I goofed up however. Below is the corrected version. The Join has to be based on whether the particular records Priority Option is standard or custom. If it's standard, it needs to match the reference_no, client_no=0 (which is standard) and the records client type must match the priority client type.
If it's custom, then the reference_no and client_no have to match.
SELECT WO.client_no, WO.wo_number, WO.priority_no, Client.client_name, Client.client_no, Client. Client_type, Priority.Priority_vw
FROM FMS.WorkOrder
LEFT JOIN FMS.Client ON WO.Client_No=Client.Client_No LEFT JOIN FMS.Priority
ON
IF ( Client.PriorityOption=("standard"),
WO.priority_ref_no=Priority.priority_ref_no And Priority.Client_No=0 And Priority.client_type=Client.client_type.,
WO.priority_ref_no=Priority.priority_ref_no And WO.Client_No=Priority.Client_No
)
SELECT ... FROM FMS.WorkOrder WO LEFT JOIN FMS.Client ON WO.Client_No=Client.Client_No LEFT JOIN FMS.Priority ON WO.priority_ref_no=Priority.priority_ref_no WHERE (Client.Client_type="School District" AND Priority.Client_No=0 AND Priority.client_type="School District") OR (WO.Client_No=Priority.Client_No)
-- Roger
-- -------------------------------------------------------------------------------- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, Technology Director; /Lasso Partner Association Member ID #LPA135259; --------------------------------------------------------------------------------
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
