joining multiple.tables..

[jyotsana dang]

i got 10 tables and got to make a join query to display data in the search page..
tried joining 3 tables..bt i get the same records..many times..
select c.project_title,m.title,p.project_title,pr.project_title
from consultancy_projects c,manuals m,phd_projects p,projects_supervised pr,login
where
c.user_login=m.user_login
and m.user_login=p.user_login
and p.user_login=pr.user_login
and login.user_login='101'
wanted to ask..is it better to make a single join query ..or to make diff .sql statements..to display data...from diff..tables in a single jsp page..
thanks..

[eammon bannon]

wanted to ask..is it better to make a single join query ..or to make diff .sql statements..to display data...from diff..tables in a single jsp page..

Which is better depends on circumstance. Generally SQL queries with lots of joins will slow the DB operation down, but a seperate SQL statement for each part of the query will require a round trip from your jsp and this is usually will have a far greater impact on the performance of your app.
Your SQL does look a little suspect though. I presume what you are after is to return consultancy_projects, manuals, phd_projects and projects_supervised records for each user? Well, unless there is a 1-1 relationship between all these tables, then you will always get redundant data returned. So it looks like you will probably need seperate queries anyway.

[Rudy Dakota]

Hi Jyotsana,
First of all, it is extremely difficult to say something sensible about a statement when you don't know the underlying tables. This being said, if your statement results in the same rows being returned over and over, there 's probably someting wrong with it. One thought that struck me as I read Eammons reply is that you are very likely to need outer joins, as there will probabaly be cases where matching rows will not be present in all tables (imagine giving an overview of classmates in college with their brothers and sisters; there will be those who have none, yet you want them in the result anyway). So, point one is probably: write a correct statement and test it.
For the rest I agree with Eammon (hope I got that right): your additional roundtrips will be more costly. And I 'm not even talking about your join algoritm. These RDBMS guys make a living out of it since ages. They probably know what they do.
Good riding,
Rudy.

[Jeanne Boyarsky]

Jyotsana,
If you rewrite the where clause to something like the following it will be faster too.

where login.user_login='101'
and login.user_login = c.user_loging 
and ... 

The database can filter out a lot of rows if it only has to do the joins for one login. (Some databases optimize this away, but it's still a good practice.)

[jyotsana dang]

just wanted to explain my database..
i have 6 tables(which have information on different projects) with an identity column in each of them. The main table is login with user_login as the primary key field. The rest of the tables have user_login as the foreign key field..
iam trying to display from every table the titles of the projects and display them on the results page..
i guess will work on outer joins in the same..but if anyone has a better solution for this..please guide me..
thanks