select if a value is in any field

[grissom]

Hi folks, hope you can help with this one

 

Imagine I have a database which contains details of where a person was born, where they went to school, where they went to college and where they now live and where they now work.  The table might look like this :

 

name            born                school          college            live                work

-------            -------              --------          ----------          -----                --------

Alice              Washington    Orlando        Miami              Las Vegas      Los Angeles

Bob              Orlando            Miami          Daytona          New York      Chicago

Bill                Chicago            New York    Baton Rouge    Little Rock      Houston

 

What I would like to do is return the rows of all people who had any connection to Chicago  (in this case Bob and Bill), in other words (in pseudo code) SELECT * WHERE any field = 'Chicago'

 

 

Of course, I could always use an "OR" statement. But what if my database had, say, 100 fields.  Is there a really slick way to do it ?

 

many thanks

[trq]

SELECT * FROM tbl WHERE 'chicago' IN(name, born, school, collage, live, work);

[Mchl]

But what if my database had, say, 100 fields.

 

Then it would be badly designed