Sql Server Programming :: Converting Non-ANSI joins to ANSI joins


	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... - Hide quoted text - > 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 Thanks a lot Dan On May 23, 5:41 am, "Dan Guzman" <guzma...@nospam- - Hide quoted text - online.sbcglobal.net> wrote: > > 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... > > 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- Hide quoted text - > 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... - Hide quoted text - > Thanks a lot Dan > On May 23, 5:41 am, "Dan Guzman" <guzma...@nospam- > online.sbcglobal.net> wrote: >> > 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... >> > 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- Hide quoted text - >> 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 - Hide quoted text - Shocky wrote: > 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