SQL - Join How?

[Brett Harvey]

--============_-1151307064==_ma============
Content-Type: text/plain; charset="us-ascii" ; format="flowed"

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;
--------------------------------------------------------------------------------
--============_-1151307064==_ma============--