Need some logic help

Trying to bind some data to a datalist for a report.

User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.

If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.

I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.

Any thoughts on how I can do this?I can think of a couple of ways to accomplish this, but the most elegant involves using right outer joins and the COALESE function. COALESE is a function that returns the first non-null expression from a list of parameters. So the SELECT statement would be something like the following

SELECT COALESE(TABLE_ONE.NAME, TABLE_TWO.NAME, TABLE_THREE.NAME)
FROM TABLE RIGHT OUTER JOIN TABLE_ONE ON (TABLE.FIELD = TABLE_ONE.KEY AND TABLE.FIELD_ONE = 'A')
RIGHT OUTER JOIN TABLE_TWO ON (TABLE.FIELD = TABLE_TWO.KEY AND TABLE.FIELD_ONE = 'B')
RIGHT OUTER JOIN TABLE_THREE ON (TABLE.FIELD = TABLE_THREE.KEY AND TABLE.FIELD_ONE = 'C')

The returned field will contain whichever value is not null, the one that is appropriate depending on the value of FIELD_ONE.

HTH|||Am I understanding that the lookup values are in different tables. If so then you'd be doing conditional joins which I don't think is possible. Assuming you can't change the table structure around, I would create a query that combines all the lookup information from Table_One, Table_two, and Table_three (and others) and then join this table to your original table. Something like:

Select YourMainTable.Field1, B.Name
From YourMainTable INNER JOIN
(
Select Field1,Name
From
(
Select Field1,Name FROM Table1
Union
Select Field1,Name FROM Table2
Union
Select Field1,Name FROM Table3
) A
) B ON(YourMainTable.Field1 = B.Field1)

sql