MySQL Fulltext search -- multiple tables

Question

I've currently got 3 tables (neighborhoods, cities, states) and I'm trying to list the top 10 best matches (if not an actual match) for a given search term. What I need to do is have it so if there is a neighborhood name match, have it rank higher than a city name match. What confuses me is that if it finds a neighborhood, I'd need it to give me the neighborhood, the city and the state but if its a city, just give me the city and state.

This is what I have at the moment, which just gives me city matches. How can I mix neighborhoods into those results?

SELECT c.id_city, c.city_name, s.state_abbreviation, MATCH(c.city_name) AGAINST ('term') AS score
FROM res_geo_cities AS c, res_geo_states AS s
WHERE MATCH(c.city_name) AGAINST ('term' IN BOOLEAN MODE) AND s.id_state = c.id_state
ORDER BY score DESC
LIMIT 7

Answer 1

Use union for the different queries and order them by the rank.

(query1) UNION (query2) ORDER BY score DESC

keep the numbers of fields in the queries the same and tha same order. - http://dev.mysql.com/doc/refman/5.0/en/union.html