How many tables can a join have max ?

[Mallika Kumar]

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]

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]

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]

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