MySql multiple table Select problem

Adam

Hi guys, little stuck on this. I've got 5 tables for 5 different categories, sales lettings, sharing, short term and commercial. They can each be searched individually using advanced search pages, but I want to have a quick search option as well, that will give back all the results from each table based on the location. Anyone got any pointers on how to go about this?

I've tried this:

[PHP]$query = 'select * from items_sale, items_let, items_share, items_sterm, items_comm as items where items.active = "Yes" and items.sold = "No" order by items.hits desc limit 4';[/PHP]

but that doesn't return the necessary result set, I can see why, it was just a stab in the dark!

liamo

You're looking for a UNION'ed query

eg

Select field1 as SomeName from Table1
union
Select Field5 as SomeName from Table2
union
Select Fieldx as SomeName from Table3

That should sort you out.

Regards

Liam

Adam

Cheers liamo, thats set me on the way! This is what I get for learning by doing, I miss out on the likes of unions and subqueries!