SQL Server T-SQL Query Optimization -

i trying optimize following t-sql query:

select person.* person zipcode '123%' , city = 'washington' , numberofhomes in (1, 2, 3) , (     exists     (         select * house         person.id = house.personid         , house.type = 'townhouse'         , house.size = 'medium'     )     or     exists     (         select * color         person.id = color.personid         , color.foreground in ('green', 'blue', 'purple')     ) ) 

i'd appreciate response in optimizing query.

in particular, there way convert query more efficient query using single select statement without of inner select statements?

thanks!

this query:

select p.*  person p p.zipcode '123%'  , p.city = 'washington' , p.numberofhomes in (1, 2, 3) ,       (exists (select *                house h                p.id = h.personid , h.type = 'townhouse' , h.size = 'medium'              ) or         exists (select *                color c                p.id = c.personid , c.foreground in ('green', 'blue', 'purple')               )       ); 

without rewriting query, can optimize indexes. recommend:

person(city, zipcode, numberofhomes, id); house(personid, type, size); color(personid, foreground) 

question, though. sure ids in thehouseandcolortables match toperson.id? normally, have column called likepersonid`.