one query instead of four - is it possible?

Question

i must get data from four tables. i wrote the script with four queries, but i use it in ajax, and i wan't to do it by one query. here is queries...

$query1 = "SELECT `id`,`name_ar` FROM `tour_type` ORDER BY `order`";
$query2 = "SELECT `id`,`name_ar` FROM `hotel_type` ORDER BY `order`";
$query3 = "SELECT `id`,`name_ar` FROM `food_type` ORDER BY `order`";
$query4 = "SELECT `id`,`name_ar` FROM `cities` WHERE `id_parrent` = '$id_parrent' ORDER BY `name_ar`";

is it possible to write in one query? thanks

structure of tables tour_type

id | name_ar | name_ru | name_en | order |

food_type
id | name_ar | name_ru | name_en | order |

hotel_type
id | name_ar | name_ru | name_en | order |

cities
id | id_parrent | name_ar | name_ru | name_en | order |

Answer 1

A quick and easy way would be to try a UNION of the four select statements. As long as the number of columns is the same and the column names are the same, your database system should let you get away with it.

We maybe shouldn't get into relational modeling/design since there are many differing opinions and styles, but... If they're all just "types" then maybe they should be in a single table. So many different ways to skin this cat.

Answer 2

Sure, use a UNION. You could include a pseudo-column about the source of the row if it matters to you:

SELECT id,name_ar,'tour' FROM tour_type
UNION
SELECT id,name_ar,'hotel' FROM hotel_type
UNION
SELECT id,name_ar,'food' FROM food_type
UNION
SELECT id,name_ar,'cities' FROM cities WHERE id_parrent = $id_parrent

You may need to play around with whatever ORDER you want.

Answer 3

I think you can do it using UNION like this one:

SELECT `id`,`name_ar` FROM `tour_type` ORDER BY `order`

UNION

SELECT `id`,`name_ar` FROM `hotel_type` ORDER BY `order`

It is up to you to use UNION or use UNION ALL. the more specific examples can be viewed here Union sqlserver

Answer 4

The UNION [ALL] function will allow you to combine more than one query, but the column data type and order must be identical between all queries. Additionally, UNION will remove duplicates - making it slower than using UNION ALL (which will not remove duplicates).

That said, UNION statements don't allow for ORDER BY's for each statement, but MySQL supports this if you place the query within brackets. Use:

(SELECT id, name_ar FROM tour_type ORDER BY order)
UNION ALL
(SELECT id, name_ar FROM hotel_type ORDER BY order)
UNION ALL
(SELECT id, name_ar FROM food_type ORDER BY order)
UNION ALL
(SELECT id, name_ar FROM cities WHERE id_parent = ? ORDER BY name_ar)

Answer 5

Since you've got a consistent number of columns, you could union them together, with some kind of key identifier, like:

select 'TBL1' as table_identifier, id, name from TBL1
union
select 'TBL2' as table_identifier, id, name from TBL2
...

But 4 queries that are written well, mapped to individual objects, would be better design, and unless you are desperate for those extra milliseconds, I'd stick with good design over a quick and dirty query.