combine 4 queries into one

[naroliya]

hi friends,

it should be simple though but i dont know how to do it...

i have the following 4 queries:

select pk_id as touristplaces from city_tourist_places ctp where ctp.city_id=2

select cti.pk_id as traininfo from city_train_info cti where cti.city_id=2

select cfi.pk_id as flightinfo from city_flight_info cfi where cfi.city_id=2

select restaurant_id as restaurant from city_restaurant cr where cr.city_id=2

i want to write only one single query.

im only checking if a record exist for the specific city_id in all the above queries, please note city_id is exist in all tables.

im using mysql 4.1.12.

regards
sanjeev

[Dijkgraaf]

Well if it wasn't MySQL you could have done it with subqueries

select
(select count(pk_id) from city_tourist_places ctp where ctp.city_id=2) as touristplaces,
(select count(cti.pk_id) from city_train_info cti where cti.city_id=2) as traininfo,
(select count(cfi.pk_id) from city_flight_info cfi where cfi.city_id=2) as flightinfo,
(select countr(restaurant_id) from city_restaurant cr where cr.city_id=2) as restaurant

However as MySQL doesn't support that you might be out of luck.
Instead you could have

select 'touristplaces' tblname, count(pk_id) as occurences from city_tourist_places ctp where ctp.city_id=2
union
select 'traininfo', count(cti.pk_id) from city_train_info cti where cti.city_id=2
union
select 'flightinfo', count(cfi.pk_id) from city_flight_info cfi where cfi.city_id=2
union
select 'restaurant', countr(restaurant_id) from city_restaurant cr where cr.city_id=2

which will give you a record set countaining four rows with the number of occurences in each table.

[Demaestro]

Here you go.

select
ctp.pk_id as touristplaces,
cti.pk_id as traininfo,
cfi.pk_id as flightinfo,
cr.restaurant_id as restaurant
from
city_tourist_places ctp
left join
city_train_info cti
on ctp.city_id = cti.city_id
left join
city_flight_info cfi
on cfi.city_id = ctp.city_id
left join
city_restaurant cr
on cr.city_id = ctp.city_id
where
ctp.city_id=2

[Dijkgraaf]

The example that Demaestro has given will only be good if a particular value of city_id only appears once in city_tourist_places and 0 or 1 times in the other tables.
If you have multiple occurences of the same city_id in tables, or 0 in tourist, then you will get some odd results.

[Demaestro]

That is true, my example assumes that there will be at least 1 record of ctp.city_id = 2

[Demaestro]

To make up for this you could go like this:

select
ctp.pk_id as touristplaces,
cti.pk_id as traininfo,
cfi.pk_id as flightinfo,
cr.restaurant_id as restaurant
from
city_tourist_places ctp
left join
city_train_info cti
on ctp.city_id = cti.city_id
left join
city_flight_info cfi
on cfi.city_id = ctp.city_id
left join
city_restaurant cr
on cr.city_id = ctp.city_id
where
(
ctp.city_id=2
or cti.city_id=2
or cfi.city_id = 2
or cr.city_id = 2
)

[chrisjoha]

Dijkgraaf: I'm using MySQL 4.1.14 and MySQL 5, both of which support subqueries.

[Dijkgraaf]

Well then he should be able to use the first example :-)