ok, what about if i had a table called PEOPLE with these columns: FIRSTNAME LASTNAME ADDRESS CITY STATE ZIPCODE and i wanted to find any "john smith" in the same zipcode as a "susan jones".... what would the query be? thanks!
I want to think its something like this, but it won't let both tables be the same one.... SELECT * FROM left_table people LEFT OUTER JOIN right_table people ON r.zipcode = l.zipcode and l.first = 'john' and l.last = 'field' and r.first = 'lisa' and r.last = 'field'; This seems to run ok, but runs out of memory... am i doing this entirely the wrong way? SELECT * FROM people l LEFT OUTER JOIN people r ON r.zipcode = l.zipcode and l.first = 'john' and l.last = 'field' and r.first = 'lisa' and r.last = 'field';
If anyone cares, I figured it out.... here's a fast working example: SELECT t1.*,t2.* FROM people t1 INNER JOIN people t2 ON t1.zipcode=t2.zipcode AND t1.first= 'john' and t1.last = 'smith' and t2.first = 'susan' and t2.last = 'smith';