Ok Imagine this scenario... now I am just inheriting this and not the original architect.... And my apologies for being generic but to show real data would make it even more confusing.

So say you have a table with 500 clients, but some are Diferent Branches

So say the 1st client is Acme SQL Servers (like that one? :))

Acme SQL Servers is in Field A

In Field B there are East, West, Central

In Field C there are North East, North West, South East, SouthWest, East Central, West Central

So it looks like this

Acme SQL Servers East North East

Acme SQL Servers East South East

Acme Routers

Acme Computers East

Acme Computers West

Acme Hard Drives East North West

And you have a sales table that you want to join it with

Now here is where it gets interesting:

Inserting a key in the sales data is not an option.

There are some fields in the sales table that will allow you to extrapulate who the client was but this is what is requiring the cases...

If a certain code has data in Field A,B & C then we need to join on a certain field

If there is no data in c but there is in A & B then we would join on a different field

If there is no data in b & c but there is in A then we would join on a different field

The only way I could think of doing it (but dont laugh here, it seems to work) is:

Select all the data where field C is not null

UNION

Select all the data where field c is null but field a & b are not null

UNION

Select all the data where field B & C Are null but not A

This is a very taxing pull on the server because we are talking about returning anywhere from 1-8 million records.