3 table join problem

[oracle765]

SELECT R.RegionName, A.City 
FROM 
`activepropertylist` A,
`ParentRegionList` R,
`RegionEANHotelIDMapping` RM
WHERE MATCH(City, RegionName) AGAINST ('hervey bay' IN BOOLEAN MODE)
and A.EANHotelID = RM.EANHotelID
and RM.RegionID = R.RegionID
and R.RegionType = 'Neighborhood'

schema

http://developer.ean.com/database-catalogs/relational/geography-data/

book a suit search box example

http://www.bookasuite.com/

I am trying to join 3 tables together to get the correct information from expedia tables for my auto drop down box

 

the schema is here, located in the link and I am looking auto search like book a suite do(link also provided)

 

problem is I can manage to get the word hervey bay out of the property region list table but the incorrect city comes back from the activeproperylist table(it seems to be showing something from the other table for the city)

 

 

here is my query

 

 

 

 

[oracle765]

MY QUERY

SELECT City, Country
FROM `activepropertylist`
WHERE MATCH(City) AGAINST ('+"hervey bay"' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country

union all
SELECT RegionName,RegionID
FROM `parentregionlist`
WHERE MATCH(RegionName) AGAINST ('+"hervey bay"' IN BOOLEAN MODE)

RESULTS


City,Country  
 
Hervey Bay 6053026 

Hervey Bay Golf and Country Club 6217212 

Hervey Bay Golf and Country Club 6217213 

Hervey Bay Historical Village Museum 6217250 

Hervey Bay Historical Village Museum 6217253 

Hervey Bay Botanic Garden 6217257 

Hervey Bay Botanic Garden 6217259 

When really it should be

RegionName,RegionID  
 
Hervey Bay 6053026 

Hervey Bay Golf and Country Club 6217212

I have an update. Apparently I only need to look into two tables for the place, so what I am trying to do is find out of the keyword "hervey bay" exists in the activeproperty list table.  If it does not does it exist in the parentregionlist table

 

but I am getting some strange results there seems to be something I am missing as it only shows the two columns from the parentregionlist table when I want to show all 4

 

here is my query and results

Edited August 10, 2014 by oracle765

[mac_gyver]

if the tables have a defined relationship (we/i don't visit unknown links) that you are trying to join together to return the correct related data for each row, you need to get the joined query to work correctly first before you try to add any conditions to filter the results.

 

i also recommend putting the join conditions with the join terms, not in the where clause. this a rearrangement only of the first posted query (doesn't attempt to fix anything about the logic) - 

FROM `activepropertylist` A
JOIN `RegionEANHotelIDMapping` RM ON A.EANHotelID = RM.EANHotelID
JOIN `ParentRegionList` R ON RM.RegionID = R.RegionID
WHERE MATCH(City, RegionName) AGAINST ('hervey bay' IN BOOLEAN MODE)

you would only use a UNION query for similar, not related, data in multiple tables (you must select the same number and type of columns in each query and the column names from the first query is what is used for all the data.)

 

showing some sample data from each table, what relationship there is between the tables, what column(s) from each table you want to filter, and what result you expect from that sample data would be the quickest way of solving the puzzle.

[oracle765]

Hi thanks for that I now have

SELECT R.RegionName, A.City,A.StateProvince,A.Country,A.CountryName
FROM `activepropertylist` A
JOIN `RegionEANHotelIDMapping` RM ON A.EANHotelID = RM.EANHotelID
JOIN `parentregionlist` R ON RM.RegionID = R.RegionID
WHERE MATCH(City, RegionName) AGAINST ('hervey bay' IN BOOLEAN MODE)

which works

I have a fulltext index on both the City & RegionName but the trouble is it is taking approx. 7 seconds to return in the backend DB, If I make this live it is more than likely going to be longer which would make the user sit and wait for wuite sometime when searching on an area from the drop down box

 

Is there a way to speed it up any further?

 

here is the schema image attached for further information

 

Basically what I am trying to do is be able to let the user pick from my search box an area, which I am using for auto predict just like expedia do with there auto search for hotels

Edited August 11, 2014 by oracle765