I've done a fair amount of experimenting, and I'm struggling to find an SQL query to search multiple tables in the same database simultaneously.
I have 3 tables in the same database, all containing different data, but they each have a field that contains the name of a town. Some towns are represented in more than one table, and some are only represented in one table. I need to produce a list of the towns in the 3 tables, but without any duplicates in the list.
For a town to be included in the list, some fields in its record must match certain criteria. Something like this...
town name, county name, country name, accommodation type (campsite, guest house, etc.)
If the county field, country field, and type field match, then I need the record's town name to be included in the list, and I need that to happen from all 3 tables. But I need a town to be listed only once (distinct), even if it is in more than once table. Some of the field names are different in the 3 tables (e.g. the town field is called 'town' in 2 tables, but it's called 'address3' in another one). One of the tables doesn't have a field for the accommodation type, because it only contains one type.
I hope I've explained the problem clearly enough, and I hope that someone can help me out.
I should say that it uses MySQL 3.x