RE: Converting Inner join in MS-SQL to Oracle SQL syntax

Yep, looks good (I'm a SQL7/2000 DBA).

"INNER JOIN" etc.. is not SQL Server specific, It's just ANSI standard.

If you have.....
select col1,col2,col3
from table1 inner join table2 on table1.id=table2.id

you can then change this to the following to get it to work on Oracle....

select col1,col2,col3
from table1, table2
where table1.id=table2.id

Ade

-----Original Message-----
Sent: 03 August 2001 23:28
To: ORACLE-L_at_fatcity.com

In short, a Micro$oft T-SQL "inner join" is a PL/SQL regular "join". So (making a few assumptions about the structure of the tables), here is my crack at a rewrite:

select KO.KS_KNOWLEDGEOBJECTID
  from KS_KNOWLEDGEOBJECT KO
 where KO.KS_AVAILABLE = 0
   and not exists (select CS.KS_KNOWLEDGEOBJECTID

                     from KS_CONCEPTSTRING CS
                        , KS_CONCEPT C
                    where CS.KS_CONCEPTID = C.KS_CONCEPTID
                      and KO.KS_KNOWLEDGEOBJECTID = CS.KS_KNOWLEDGEOBJECTID
                      and C.KS_CONCEPTTYPEID = '2');

Jon Walthour
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, August 03, 2001 1:10 PM

> A developer has sent me a MS-SQL statement that he recieved from a vendor.
> He would like to convert this to run on Oracle. It's pretty close already
> but I'm
> having trouble with the inner join.
>
> Is there a kind soul familiar with MS-SQL that could convert this over to
> the
> right 8.0.4 Oracle syntax?
>
> select KS_KNOWLEDGEOBJECTID from KS_KNOWLEDGEOBJECT
> where KS_AVAILABLE = 0 and not exists
> (select KS_KNOWLEDGEOBJECTID from KS_CONCEPTSTRING inner join
> KS_CONCEPT
> on KS_CONCEPTSTRING.KS_CONCEPTID = KS_CONCEPT.KS_CONCEPTID
> where KS_KNOWLEDGEOBJECT.KS_KNOWLEDGEOBJECTID =
> KS_CONCEPTSTRING.KS_KNOWLEDGEOBJECTID
> and KS_CONCEPTTYPEID = '2')
>
>
> Thanks for any assistance,
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Cherie_Machler_at_gelco.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jon Walthour
  INET: jonw_at_fuse.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

------------------------------------------------------------------------------
Live Life in Broadband
www.telewest.co.uk


The information transmitted is intended only for the person or entity to which it is 
addressed and may contain confidential and/or privileged material.
Statements and opinions expressed in this e-mail may not represent those of the 
company. Any review, retransmission, dissemination or other use of, or taking of any 
action in reliance upon, this information by persons or entities other than the 
intended recipient is prohibited. If you received this in error, please contact the 
sender immediately and delete the material from any computer.


==============================================================================

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Adrian Roe
  INET: Adrian.Roe_at_telewest.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).