Re: Joining tables in Oracle?

Thanks for your help, Brian. It helped me get started but my query was getting complicated. I searched the web for a visual tool to help me and I came across EZSQL (www.ezsql.net) which includes a visual query builder, a bit like Access, and it took a big strain off my brain and the SQL was written for me. It's the best $100 I've spent in a long time. Okay, I'm being lazy but if I can get the job done quicker.... :-)

Best regards,
Gary.

"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in message news:3a4a4da7.3181168750_at_news.alt.net...
> On Tue, 26 Dec 2000 23:36:26 -0000, "Gary" <garygfx_at_hotmail.com>
> wrote:
>
> >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.
> >
> >
> >
> I posted this recently in reply to someone elses question. Maybe it
> will help.
>
> Just went through this myself, I think I understand, so here goes:
>
> LEFT JOIN and RIGHT JOIN are merely synonyms for an OUTER JOIN.
>
> In Access the following statements are equal, or so I believe.
>
> SELECT Moo.Id FROM Cow LEFT JOIN Moo ON Cow.Moo = Moo.Id;
> SELECT Moo.Id FROM Cow LEFT OUTER JOIN Moo ON Cow.Moo = Moo.Id;
>
> The word LEFT or RIGHT merely specifies which table has the columns
> that are *not* referenced in the other table. So, in the above
> example, if table "Cow" has ids that are not in the "Moo" table, a
> NULL will show up as the result for that row (Moo.Id). This is because
> it is a LEFT join, and the table on the left of the join statement is
> "Cow". So LEFT or RIGHT specifies the table that has the extra rows.
>
> An INNER JOIN, in Access can be specified as
>
> SELECT Moo.Id FROM Cow INNER JOIN Moo ON Cow.Moo = Moo.Id;
>
> - or -
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id;
>
> These will render identical results.
>
> Now Oracle can handle INNER JOINs in the latter method
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id;
>
> To make it an OUTER Join you add the (+) symbol to the column of the
> table that is being referenced, that is the one that *must* have the
> values, or return a null. This is the exact *opposite* of Access.
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id(+);
>
> In Access this is a LEFT JOIN. For a RIGHT JOIN:
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo(+) = Moo.Id;
>
> If I'm wrong, please tell me, I just dived into outer joins, for both
> Access and Oracle, yesterday...
>
> Brian
Received on Sat Dec 30 2000 - 19:19:48 CST