File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes joining multiple.tables.. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "joining multiple.tables.." Watch "joining multiple.tables.." New topic

joining multiple.tables..

jyotsana dang
Ranch Hand

Joined: Sep 26, 2003
Posts: 135
i got 10 tables and got to make a join query to display data in the search page..
tried joining 3 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
and m.user_login=p.user_login
and p.user_login=pr.user_login
and login.user_login='101'
wanted to it better to make a single join query ..or to make diff .sql display data...from diff..tables in a single jsp page..
eammon bannon
Ranch Hand

Joined: Mar 16, 2004
Posts: 140

wanted to it better to make a single join query ..or to make diff .sql 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

Joined: Jul 27, 2002
Posts: 54
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,
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

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.)

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
jyotsana dang
Ranch Hand

Joined: Sep 26, 2003
Posts: 135
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..
I agree. Here's the link:
subject: joining multiple.tables..
It's not a secret anymore!