Topic: Search in two bound tables

Good afternoon!
I have two tables " Streets " and " Structures ". Structures are anchored to " to Streets " It is necessary to carry out search of the necessary structure having retrieval request in the form of the text (if retrieval the request is not exact - some results are possible).
Lines have as "", those different variations of a spelling (title: , sub_titles: |), those the street can have synonyms in a title. (The storage format  can be any as you tell and I will store).
For example: " Chapaeva 14 ", " Chapaeva ", " street Chapaeva 14 ", " 14 Chapaeva "

` streets ` (
` street_id ` smallint (5) unsigned NOT NULL AUTO_INCREMENT;
` title ` varchar (100) NOT NULL;
` sub_titles ` varchar (255) DEFAULT NULL;
PRIMARY KEY (` street_id `)
` locations ` (
` location_id ` smallint (5) unsigned NOT NULL AUTO_INCREMENT;
` street_id ` smallint (5) unsigned DEFAULT NULL;
` title ` varchar (100) NOT NULL;
PRIMARY KEY (` location_id `)

It is necessary to receive identifiers of structures approaching under retrieval request.
Any help and councils is useful. Even the request description in "human" language is simple, will try if that it to make.


Re: Search in two bound tables

p.s. I understand that it MySQL, but a table essence I think is clear. If that I understand as to adapt request under MySQL. Councils approaches on any language smile


Re: Search in two bound tables

And how to distinguish
Building 3
3 Building (the third building)
And will be still:
Building 3
3rd building


Re: Search in two bound tables

982183, I need to make the list of assumptions (helps) by search. Those it is possible to consider as the correct result and "the third building street" and "the third house along the street building". Here strongly accuracy is not necessary  such collisions especially does not come in dream, and this search is made for helps of the necessary variant. The user selects a finite variant from the list which will be made in the correct format of data output where it will be clear where "3rd building", and where "Building, .3".
So I think it it is possible to neglect.


Re: Search in two bound tables

Whether instead of it is easier, using , to make address entry form?