Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How many tables can a join have max ?

 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 320
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).]
 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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. )
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic