Hi there, Let's say you want to count the number of field, from several different tables in your database. This is an example of the text I want on my site: "We have 344 destinations, 87 hotels, 35 resturant and 36 attractions in our database". Is this possible to do in one single query? And would it be smart to cache the result?
if they're in different tables or you need different where clauses, you need different queries only if they're all in 1 table and you also have a category column for each line you could use count and group by. if the sums are shown much more than they change it's a good idea to cache the result.. (ie they're updated once a day and queried thousands of times a day) although if you put a good index on that table then sql server will cache it too for you either schedule a job that runs every few hours and updates a table with the sums, or install triggers or change the application so that every add/delete will change the sum values too
use subqueries and use subquery results in select statement like below; select (select count(*) from destinations), (select count(*) from hotels), (select count(*) from resturants), (select count(*) from attractions)