*
The moose likes JDBC and the fly likes How many tables can a join have max ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How many tables can a join have max ?" Watch "How many tables can a join have max ?" New topic
Author

How many tables can a join have max ?

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
HI,
How many tables can a join have max ? When I create a join with some conditions between 4 tables, it works, But the same join, when I add another condition and another table, it does not work. I get an error saying :
ORA-01747: invalid user.table.column, table.column, or column specification
Can anyone shed more light on this ? Any help is appreciated.
Thanks.
John Bateman
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
Hi
As far as I know the limit of table joins you can perform are dependant on the engine you are running them on. Suffice it to say I've seen table joins of 20 or more. Now I won't commment on how badly built these systems were, but hey.. the joins worked.

Can you post some code here for us to look at?
Thanks.
[This message has been edited by John Bateman (edited April 04, 2001).]


SOURCE CODE should be SURROUNDED by "code" tags.
Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
Here's some code:
This join works......
select u.login,a.email from user u,personuser pu,person p,subscription sub,address a where u.userid=pu.userid and pu.userid=sub.userid and pu.personid=p.personid and p.addressid=a.addressid ;
Now, when I add another table to this and another condition,it does not work.
select u.login,a.email,i.answer from user u,personuser pu,person p,subscription sub,address a,identityqa i where su.userid=pu.userid and pu.userid=sub.userid and pu.personid=p.personid and p.addressid=a.addressid and p.identityqaid=i.identityqaid;
What am I missing here ?
Thanks.
Monty Ireland
Ranch Hand

Joined: Oct 03, 2000
Posts: 161
You have a typo...
select u.login,a.email,i.answer from user u,personuser pu,person p,subscription sub,address a,identityqa i where su.userid=pu.userid and pu.userid=sub.userid and pu.personid=p.personid and p.addressid=a.addressid and p.identityqaid=i.identityqaid;
Chg su to sub...
Table join limit depends upon the datbase engine....
LT 6 table in a join are very common...
GT 12 tables are very uncommon...
I have coded with 16 table's using LEFT OUTER JOIN's w/ DB2.
Hope this helps....

------------------
Multi-Platform Database Developer ( on E.S.T. )


Multi Platform Database Developer & DBA on E.S.T.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How many tables can a join have max ?
 
Similar Threads
Mapping Query
Preventing SQL Injection in Dynamic SQL
Old style SQL vs new style SQL
Join
Hibernate Criteria