Sorry if my question are quite noob here. But suppose I have to search the database for the main table named: X, but to show the result, there are lots of tables depend on X like: States, Cities, Districts, Countries, users,...The question is should I JOIN all the tables together so that I use just one query - or I search using minimal query, then for each result row, I use other queries to fetch the details? Thanks.
Personally, i would left outer join all queries, get your RS and close the DB connection. Then use variables in a huge loop to detect when a state or city etc... has changed. (just my 2 cents though... I always try to keep as much as possible in the query)
No, I am planning, no queries yet. I am just consider which is the best way to do? One query with lots of JOIN or several queries.
There is no hard and fast rule, Multiple joins will take sometime as taken by multiple queries. In fact with multiple queries, you can fetch data in a more better way (properly structured and linked).
Try to do a little joining across tables as possible but sometimes it is impossible not to. It also depends on the number of records in each table. If you are using MSSQL there is a handy tool called SQL Query Profiler which will show you how a query is going to run and lets you optimise your queries. Cheers MarkusJ
With MySQL, a lot (more than 5) tables with a bunch of left joins could be slow. If you get to that point it might be a good idea to start splitting the queries.