MySQL - querying multiple tables simultaneously

[PhilC]

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...

Criteria given:
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

[R e b r a n d t]

Well... what kind version of MySQL server you are using? If it is 4.1.2+ then maybe UNION [dev.mysql.com] will help you?

[PhilC]

No, it's MySQL 3.x

I came across UNION, and the pre-version 4 alternative of creating a temporary table, which I tried, but I didn't get anywhere with it. I simply don't understand the various JOINs or I might have half a chance at working out the solution.

[R e b r a n d t]

Ok. If you could provite full table names/full column names (table schemas) i might be able to help.

[PhilC]

Thank you. The info is...

table1
------
field: "town" varchar(60)
field: "county" varchar(60)
field: "country" varchar(20)
field: "categories" varchar(10)

table2
------
field: "town" varchar(60)
field: "county" varchar(60)
field: "country" varchar(20)
field: "categories" varchar(10)

table3
------
field: "address3" = varchar(40)
field: "county" varchar(60)
field: "country" varchar(25)
field: doesn't exist - all entries are for one category - "H" (hotels).

The "categories" field in "table1" and "table2" can contain several category characters (e.g. "CS" = "C"amping and "S"elf catering)

The query is supplied with:-
"county" - name
"country" - name
"categories" - a single character (e.g. "C" for campsites).

I need a list of DISTINCT town names from records that match the "county" AND the "country", AND where the "categories" field (table1 and table2) contains the categories character (e.g. "C"). table3 only needs to be included if the "categories" character = "H" or "A" ("A" = all categories)

The town names are in "towns", "towns" and "address3" respectively.

I'm writing it in PHP, and I can handle the conditional inclusion of table3, depending on whether or not the "categories" character is "H" or "A", if I know what piece of query string to include.

I hope I've written everything so that you can understand it all.

cheers...

[R e b r a n d t]

(-;

The table structure is very nasty, no unique ids, no nothing that could help. So temporary tables is maybe the only solutions i can think about.

How should it go:

1) you create a temporary table with the 1st query. It should look something like this:

CREATE TEMPORARY TABLE tmp (
 town varchar(60) NOT NULL default ''
) TYPE=MyISAM;

2) you fill the "tmp" table with data from table1, table2 and table 3 if needed. Those 2 or 3 queries will look something like this:

INSERT INTO tmp (town)
SELECT town FROM table1
WHERE
county = '$county_value' AND
country = '$country_value' AND
categories LIKE '%$category_value%';

INSERT INTO tmp (town)
SELECT town FROM table2
WHERE
county = '$county_value' AND
country = '$country_value' AND
categories LIKE '%$category_value%';

execute this only if category equals "A" or "H"?:

INSERT INTO tmp (town)
SELECT address3 FROM table3
WHERE
county = '$county_value' AND
country = '$country_value'

3) Now you should be able to select distinct towns from the tmp table:

SELECT DISTINCT town FROM tmp;

(-;

I havent tested anything but i think you have got the idea and will be able to fix small/minor bugs?

[PhilC]

That looks good. I use MySQL a lot but I only use it very basically. For instance, I'd no idea that you can have a query that INSERTS into one table the results of a SELECT from another table, all in one query.

I'll try it out and post back here. Many thanks :)

[PhilC]

It's working excellently. Again, many thanks for your help :)