Multiple table search

Anon

i am trying to search in multiple tables with following query:

SELECT * FROM asia, middle_east, europe WHERE asia.heading OR middle_east.heading OR europe.heading LIKE '%$heading'

above doesnt work... i even tried:

SELECT * FROM asia, middle_east, europe WHERE asia.heading LIKE '%$heading' OR middle_east.heading LIKE '%$heading' OR europe.heading LIKE '%$heading'

still no records are fetched!

the field name 'heading' is same in all tables.

and secondly upon results, how do i get the table name, where the result came from?? i tried mysql_tablename() but that gives me the auto_increment field's value of the result!

thanks for the help,
Daarius ...

Anon

Hi Mate,

I could be wrong about this about this but since no one else has replied yet I thought I would give you my two pence.

My understanding of the sql statement when you include multiple tables is that a join in inferred between the tables. Now since you are not actually saying field 1 in table 1 is equal to field 1 in table two (example), that is why you are not getting any data returned.

The easiest thing would be to simply call three seperate sql queries, one for each table. This would also solve your problem with knowing what table is returning the data.

Cheers

Ray

Anon

thanks for reply.

but that raises another problem. now what if we want the result to be displayed by relevance of the search's criteria been mathced? i would be displaying them in turns of the table i searched in and not the original result relevance.

thanks,
Daarius...

Anon

Hi Mate,

Well depending on how complicated your matching criteria is all you need to do is to have an associative array which is updated with the details for each record(ie record id, score). That way you would have a global scoring system, and it would not matter what order the queries where submitted in.

Cheers

Ray

Anon

Ray was right in his answer. But it's possible to 'join' this 3 separate select statements into 'one' using UNION clause. Look into SQL language reference how to use it.

Anon

How can I empty all tables in on time when using MSSQL7.0?

Thanks.