Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Converting Non-ANSI joins to ANSI joins


Hi,

i have been given a query to convert its non-ansi joins to ansi
compliant joins.
i am having difficulty in doing so, as it is a bit complex (one table
has join conditions with more than one other tables)
Can someone please help me in writing the below query with proper ANSI
joins?

SELECT  *
FROM    dbo.Trace t WITH (NOLOCK),
        dbo.Prj p WITH (NOLOCK),
        dbo.Rvw r WITH (NOLOCK),
        dbo.Dft d WITH (NOLOCK),
        dbo.Common c WITH (NOLOCK)

WHERE    t.fromowner = p.pID AND
        p.pID = d.d_owner AND
                p.pID = r.r_owner AND
        t.fromid  = r.rID AND
        t.toid  = d.dID AND
        c.cID = d.dID

Thanks,
Shocky

> Can someone please help me in writing the below query with proper ANSI
> joins?

I believe the query below is equivalent.  For inner joins, you can specify
the join conditions just about anywhere as long as you don't have forward
table references.

SELECT *
FROM dbo.Trace t WITH (NOLOCK)
JOIN dbo.Prj p WITH (NOLOCK) ON
    t.fromowner = p.pID
JOIN dbo.Rvw r WITH (NOLOCK) ON
    p.pID = r.r_owner AND
    t.fromid  = r.rID
JOIN dbo.Dft d WITH (NOLOCK) ON
    p.pID = d.d_owner AND
    t.toid  = d.dID
JOIN dbo.Common c WITH (NOLOCK) ON
    c.cID = d.dID

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Shocky" <yusuf.bhiwandiw@gmail.com> wrote in message

news:1179877499.445135.218100@x18g2000prd.googlegroups.com...

Thanks a lot Dan

On May 23, 5:41 am, "Dan Guzman" <guzma...@nospam-

I'm glad to help.

--
Dan Guzman
SQL Server MVP

"Shocky" <yusuf.bhiwandiw@gmail.com> wrote in message

news:1179886582.194592.298130@g4g2000hsf.googlegroups.com...

I guess Dan gave the answer you were looking for. And I do encourage
everyone to explicitely use INNER JOIN, because IMO it avoid unnecessary
mistakes and crossjoins.

But just for the record: if you leave the locking hint out, your query
is perfectly valid ANSI SQL-92. IOW, your current query does not use a
"non-ansi join".

Gert-Jan

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc