join like clause

[jvande]

I am trying to have a query where it joins two tables where the names are like one another.

select name,school,address,city,st_abbr,zip5,phone
from temp_complete_list c
join temp_sss s on st_abbr=state_code
where name like '%'school'%' ---------This doesn't work

Is it possible to do a query like this?

 

[mkrausnick]

1. Your query joins the two tables on state code, not school name. The where clause is applied after the tables are joined.

2. I can't think of a way to do what you want, except maybe extracting the relavent portion of the name into a separate field so you can use an equality test, or using a CONTAINS test instead of LIKE. I realize these suggestions are long shots.

Lastly, for clarity, you might want to alias your field names so we could tell which fields are in which tables:

select c.name,s.school,c.address,s.city,... etc

Mike Krausnick
Dublin, California

 

[swampBoogie]

select name,school,address,city,st_abbr,zip5,phone
from temp_complete_list c
join temp_sss s on st_abbr=state_code
and name like '%' + school + '%'