CODEWITHSUNDEEP
sqloracle-databasejoin
Pztar
Pztar

Reputation: 4759

SQL - Joining 3 or more tables traditional method

I'm learning some SQL and I'd like to know what is the traditional way of joining 3 or more tables.

I know about using NATURAL JOINs, and JOIN ON. I'm thinking JOIN USING is possible too? But I heard that there is a traditional method of doing it, however, I can't figure it out.

I've tried the following: 

SELECT c#, fn, ln, cat
FROM TABLE1, TABLE4
WHERE (TABLE1.c# = TABLE2.c#
AND TABLE3.o# = TABLE2.o#
AND TABLE4.i = TABLE3.i);

But I always get the error ORA-00904: invalid identifier %s

Anybody care to help me out, or point me in the right direction?

Thanks

Upvotes: 0

Views: 3536

Answers (1)

Roger Johnson
Roger Johnson

Reputation: 158

In your example:

SELECT c#, fn, ln, cat
FROM TABLE1, TABLE4
WHERE (TABLE1.c# = TABLE2.c#
AND TABLE3.o# = TABLE2.o#
AND TABLE4.i = TABLE3.i);

... your WHERE clause references missing tables TABLE2 and TABLE3.

You would want either this:

SELECT c#, fn, ln, cat
FROM TABLE1, TABLE4
WHERE TABLE1.c# = TABLE4.c#;

OR

SELECT c#, fn, ln, cat
FROM TABLE1, TABLE2, TABLE3, TABLE4
WHERE (TABLE1.c# = TABLE2.c#
AND TABLE3.o# = TABLE2.o#
AND TABLE4.i = TABLE3.i);

... I am guessing you are using Oracle? column identifiers with '#' are valid?

Upvotes: 4

Related Questions