Re: Joining tables in Oracle?

Thanks for your reply, Walter. Unfortunately the SQL your provided didn't work (I copied and pasted it!) as no records were found. I tried changing things around but still no luck. I'm really stumpted so I'll try creating a really simple test database and play with creating basic joins.

Thanks again for your help and the pointers.

Best regards,
Gary.

"Walter Dorninger" <walter.dorninger_at_aon.at> wrote in message news:92cdud$1djf$1_at_wrath.news.nacamar.de...
> Hi Gary,
>
> (1) Inner Join should look something like this:
>
> SELECT ig_dictionary.*
> FROM ig_keywords,
> ig_dictionary,
> ig_keydict
> WHERE ig_dictionary.dictionary_id = ig_keydict.dictionary_id
> AND ig_keywords.keyword_id = ig_keydict.keyword_id
> AND ig_keywords.keyword_id='123456'
>
> (2) Outer Joins can be performed with the "(+)" Operator
>
> SELECT ename, job, dept.deptno, dname
> FROM emp, dept
> WHERE emp.deptno (+) = dept.deptno;
>
> ENAME JOB DEPTN DNAME
> ---------- --------- ---------- --------------
> CLARK MANAGER 10 ACCOUNTING
> KING PRESIDENT 10 ACCOUNTING
> MILLER CLERK 10 ACCOUNTING
> SMITH CLERK 20 RESEARCH
> ADAMS CLERK 20 RESEARCH
> FORD ANALYST 20 RESEARCH
> SCOTT ANALYST 20 RESEARCH
> JONES MANAGER 20 RESEARCH
> ALLEN SALESMAN 30 SALES
> BLAKE MANAGER 30 SALES
> MARTIN SALESMAN 30 SALES
> JAMES CLERK 30 SALES
> TURNER SALESMAN 30 SALES
> WARD SALESMAN 30 SALES
> 40 OPERATIONS
>
> another example (taken from the oracle documentation)
>
> SELECT ename, job, dept.deptno, dname
> FROM emp, dept
> WHERE emp.deptno (+) = dept.deptno
> AND job (+) = 'CLERK';
>
> ENAME JOB DEPTNO DNAME
> ---------- --------- ---------- --------------
> MILLER CLERK 10 ACCOUNTING
> SMITH CLERK 20 RESEARCH
> ADAMS CLERK 20 RESEARCH
> JAMES CLERK 30 SALES
> 40 OPERATIONS
>
> You can find all the oracle documentation on http://technet.oracle.com
 (free
> registration)
>
> Hope this helps
> Walter
> "Gary" <garygfx_at_hotmail.com> wrote in message
> news:92ba0s$fpe$1_at_plutonium.btinternet.com...
> > How can I perform a LEFT join, INNER join, and a RIGHT join in Oracle 8i
> > please? I've got some MS Access queries I need to convert to Oracles -
 it
> > seems to be a little limited on the join syntax but I'm sure there's a
 way
> > around it?
> >
> > For example, here are 2 queries I need to use with Oracle:
> >
> > SELECT ig_dictionary.*
> > FROM ig_keywords
> > INNER JOIN (ig_dictionary INNER JOIN ig_keydict ON
> > ig_dictionary.dictionary_id = ig_keydict.dictionary_id) ON
> > ig_keywords.keyword_id = ig_keydict.keyword_id
> > WHERE ig_keywords.keyword_id='123456'
> >
> > SELECT ig_keywords.keyword_id, ig_keywords.keyword,
> > ig_dictionary.dictionary_id, ig_dictionary.title
> > FROM ig_keywords
> > RIGHT JOIN (ig_dictionary LEFT JOIN ig_keydict ON
> > ig_dictionary.dictionary_id = ig_keydict.dictionary_id) ON
> > ig_keywords.keyword_id = ig_keydict.keyword_id
> > WHERE ig_keywords.keyword_id='123456'
> >
> > Thanks in advance...
> > Gary.
> >
> >
> >
>
>
Received on Wed Dec 27 2000 - 08:52:03 CST