If I understand correctly then something like this could work:

In the SELECT list you need to display TaxID or MemberID but not both. Try this:

'ID' =

CASE

WHEN et.EntityTypeID = 1 THEN MemberID

WHEN et.EntityTypeID = 2 THEN TaxID

ELSE NULL

END

The join is different. There are a few ways to do this. One is to have two seperate queries, one with the Provider table and the other with the Member table. Then simply determine before executing a query which one should run. Its likely that this one will be the best option for performance.

Another option is to join both tables as LEFT JOINS to the from clause you have. Of course this usually means you will be pulling data you don't need for any reason and that could slow down your query. I would give it a try and see if the performance is acceptable.

Then you can simply use CASE to determine what columns from each table you want to display.

I hope some of this helps.

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.