I have two tables that I want write one query that uses different where clauses under conditions in the where clause. In the first table I have type of document with document names. Through a third party program I am passing a document name as a parameter. This table also has a key that is called DoctypeID. The second table lists the possible subfolders that the documents can be stored in, which is located based on matching the DoctypeID. Certain document types can only go into one specific subfolder. i.e. a "New Hire" document can only go into the "New Hire" subfolder and "Payroll" documents can only go into the "Payroll" subfolder. However other documents can go into any subfolder. i.e. "email documents". The first attempt at writing this query is below. select distinct subfolderdescription from doctype a, subfolddesc b where if(a.doctype = "HR" and a.doctypeid = b.doctypeid, a.doctype = "HR" and a.doctypeid = b.doctypeid, b.doctypeid like "%"); This did not work the way that I intended for it to work. What I want to do is have one query that will find the record that matches the document type in the first table and if it finds a match for that record in the second table return subfolder for the matching record. But if it does not find a match to return the complete list of all the unique subfolders that are in the second table. Does anyone have any ideas on how to do this? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378
