Hello,

I need help figuring out how to query multiple columns in a table for MSSQL 2000. Here's the table layout:

Table 1
ID, First_Name, Last_Name, City, State, Country

Table 2 - Permission to view a field in table 1
T1ID, First_Name, Last_Name, City, State, Country

I want to write a query so that I can match the input to multiple fields. For example, I want to query the table 1 with the multiple words: John, Doe, Raleigh to see if all of the words, in any combination, match any row. In other words, there are 9 possible combinations of the three words John, Doe, Raleigh. And I want to return the ID for anyone that matches all three fields. Is there an easy way to do this?

The only way that I can think of doing this is:

Select * from Table1
where ((First_Name = 'John'
or Last_Name = 'John'
or City = 'John'
or State = 'John'
or Country = 'John') and
(First_Name = 'Doe'
or Last_Name = 'Doe'
or City = 'Doe'
or State = 'Doe'
or Country = 'Doe') and
(First_Name = 'Raleigh'
or Last_Name = 'Raleigh'
or City = 'Raleigh'
or State = 'Raleigh'
or Country = 'Raleigh'));

Is there a better/easier way to do this?

And to add a little more complexity, I only want to return the columns if the same ID in Table 2 has a Y in the matching column. In other words, I may find a match to John in the first name, but I only want to return the ID if the First Name for the same ID says "Yes".

Any help will be greatly appreciated.

Thanks.

Zul