Re: Outer Join Compatability in SQL Server 2000

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/19/04 

Date: Wed, 19 May 2004 13:01:30 -0400

(I tried a simpler repro, and couldn't reproduce your error, by the way.
Can you provide table structure and sample data?) 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Grant Reid" <grant.reid@telefonica(dot)net> wrote in message
news:uHoXPCcPEHA.3100@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> I have read that MS-SQL Server 2000 still supports the old syntax of doing
> outer joins.
>
> From everything I understand about the new join, these 2 statements below
> should be synonymous.  Rather strangely, only the new statement SQL-92
> syntax actually works.
>
> While I have read that it is recommended to use the new style, I have
> already written a small parser for handling the old style and creating
some
> Dynamic SQL.  I'm not really that enthused about re-writing the parser to
> handle the new syntax at the moment.  So, am I simply misunderstanding the
> joins, or is the functionality actually already starting to be removed.
>
> The old statement yields me the following error:
> Server: Msg 301, Level 16, State 1, Line 1
> Query contains an outer-join request that is not permitted.
>
> SELECT  stu.student_id
> FROM     student stu,
>                 student_product sp,
>                 location_product lp,
>                 school_location sl
> WHERE    stu.student_id *= sp.student_id
> and            sp.product_id *= lp.product_id
> and            lp.school_loc_id *= sl.school_loc_id
>
> The following statement gives me the correct result - although I don't
want
> to use it for this particular query.
> SELECT  stu.student_id
> FROM    student stu   LEFT JOIN student_product sp ON (stu.student_id =
> sp.student_id)
>                                   LEFT JOIN location_product lp ON
> (sp.product_id = lp.product_id)
>                                   LEFT JOIN school_location sl ON
> (lp.school_loc_id = sl.school_loc_id)
>
> Any assistance appreciated!
>
> Thanks
> Grant
>
>

Relevant Pages

  • Outer Join Compatability in SQL Server 2000
    ... I have read that MS-SQL Server 2000 still supports the old syntax of doing ... I'm not really that enthused about re-writing the parser to ... Query contains an outer-join request that is not permitted. ... FROM student stu LEFT JOIN student_product sp ON (stu.student_id = ...
    (microsoft.public.sqlserver.programming)
  • Re: FTS Performance in SQL 2005
    ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Date_Time Convert(24) to DateTime Format
    ... You posted a query in the original post, ... When it's in smalldatetime format then use the ... >>> select counterDateTime from CounterData ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Find matching set of items
    ... > "Given a sepcific OrderID" is how the other set would be "passed" or rather> created. ... > This isn't a homework assignment, I was just hoping someone else has had to> solve a similar problem relationally, and could at lease give me some advice> on how to approch the problem. ... >> SQL Server MVP ... >>> Without using a cursor or any kind of looping, I need to write a query ...
    (microsoft.public.sqlserver.programming)