Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

joining multiple.tables..

 
jyotsana dang
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jyotsana,
If you rewrite the where clause to something like the following it will be faster too.

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
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic