SQL - Join How?

[Brett Harvey]

Is it possible to basically do a conditional within a join (or 
perhaps this is where a subquery is needed)?

I have a workorder table that I'm doing a join with. Each Client may 
be a different client type and thus needs to be joined from one of 
two different tables.  Also, each client may choose to use a set of 
standard "priorities" we have for each client type or define their 
own.

If I was doing a SELECT that was for just one client, this wouldn't 
be a big deal because the client type and which option (standard or 
custom) would be know.  However, we want to be able to show all 
workorders from all clients at once.

The Table Structure is basically this

Client table:
        Client_no:      Int
        Client_name:    VarChar
        Client_type:    VarChar
        PriorityOption: (Custom or Standard)
WO table:
        Client_no               Int
        wo_number       Int
        priority_ref_no tinyInt
Priority Table:
        Client_no               Int
        priority_ref_no tinyInt
        priority_vw     VarChar
        Client_type:    VarChar


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.Client_type="School District",
	WO.priority_ref_no=Priority.priority_ref_no And 
Priority.Client_No=0 And Priority.client_type="School District",
	WO.priority_ref_no=Priority.priority_ref_no And 
WO.Client_No=Priority.Client_No
)

MySQL version 3.23.54
--
--------------------------------------------------------------------------------
/Brett C. Harvey;
/Creative-Pages.Net, President;
/Facility Management Systems, Technology Director;
/Lasso Partner Association Member ID #LPA135259;
--------------------------------------------------------------------------------