MySQL Forums
Forum List  »  MyISAM

Location search in mysql joining 4 tables
Posted by: Soubhik Chatterjee
Date: January 21, 2017 07:27AM


I am using MySQL db and trying to build a search for Country, state city and areas similar to the one in

In my database, I have 4 different tables (Countries, States, Cities and Areas). I have added FULLTEXT index for the "name" field in country, state, city and area tables.

Table structure

How can I join these four tables so that when I search for a string it should search in the "name" column in all the four tables and return results in a single row per match. The match should be in a specific format (Area, City, State, Country) For example: "Bourke Street, Melbourne, Victoria, Australia"

I am trying to achieve this by using the UNION syntax.

SELECT name AS area_name
FROM areas
WHERE MATCH name AGAINST('Bourke Street')
SELECT name AS city_name
FROM cities
WHERE MATCH name AGAINST('Melbourne')
SELECT name AS state_name
FROM states
WHERE MATCH name AGAINST('Victoria')
SELECT name AS country_name
FROM countries
WHERE MATCH name AGAINST('Australia')

This yields results but there are two problems with this:

1. Each result is fetched in a new row. I want the result to be like this "Bourke Street, Melbourne, Victoria, Australia".
2. If I search for "B" instead of "Bourke Street", it does not reflect any match.

Any help will be highly appreciated.

Thanks in advance

Options: ReplyQuote

Written By
Location search in mysql joining 4 tables
January 21, 2017 07:27AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.