Solved

Convert old style SQL to proper JOIN syntax

Posted on 2007-11-14
5
695 Views
Last Modified: 2010-05-18
I was looking for some assistance in converting this to the recommended SQL structure.

SELECT c.clubid, c.name, c.addr1, c.addr2, c.city,
       c.statecd, c.zipcode, c.phone, c.website,
       c.createdon, c.modifiedby, c.modifiedon,

       crs.courseid, crs.name, crs.modifiedby,
       crs.modifiedon,

       subcrs.subcourseid, subcrs.name,
       subcrs.modifiedby, subcrs.modifiedon,

       t.teeid, t.color, t.overallydg, t.overallpar,
       t.rating, t.slope, t.ydg1, t.ydg2, t.ydg3, t.ydg4,
       t.ydg5, t.ydg6, t.ydg7, t.ydg8, t.ydg9, t.parid,
       t.hdcpid, t.modifiedby, t.modifiedon

       p.parid, p.par1, p.par2, p.par3, p.par4, p.par5,
       p.par6, p.par7, p.par8, p.par9, p.modifiedby,
       p.modifiedon

       h.hdcp1, h.hdcp2, h.hdcp3, h.hdcp4, h.hdcp5,
       h.hdcp6, h.hdcp7, h.hdcp8, h.hdcp9, h.modifiedby,
       h.modifiedon

  FROM club c,
       course crs,
       subcourse subcrs,
       tee t, par p, handicap h
 WHERE c.clubid = @clubid
   AND c.clubid = crs.clubid
   AND crs.courseid = subcrs.courseid
   AND subcrs.subcourseid = t.subcourseid
   AND t.parid = p.parid
0
Comment
Question by:ApexCo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20286092
 FROM club c,
       course crs ON c.clubid = crs.clubid
       subcourse subcrs ON crs.courseid = subcrs.courseid
       tee t ON subcrs.subcourseid = t.subcourseid
       par p ON t.parid = p.parid
       handicap h ON 1=1
 WHERE c.clubid = @clubid

there seems to be no criteria for handicap so I slapped a 1=1
0
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20286099
oops..

 FROM club c
       INNER JOIN course crs ON c.clubid = crs.clubid
       INNER JOIN subcourse subcrs ON crs.courseid = subcrs.courseid
       INNER JOIN tee t ON subcrs.subcourseid = t.subcourseid
       INNER JOIN par p ON t.parid = p.parid
       INNER JOIN handicap h ON 1=1
 WHERE c.clubid = @clubid
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20286108
FROM club c  
       inner join  course crs on c.clubid = crs.clubid
       iner join subcourse subcrs crs.courseid = subcrs.courseid
       inner join tee t on subcrs.subcourseid = t.subcourseid
       inner join par p on  t.parid = p.parid
       handicap h  on 1= 1  --- ?
 WHERE c.clubid = @clubid
 
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20286118
some DBAs who shall remain unnamed throw a hiss and a fit when they see 1=1, so this is also valid

 FROM club c
       INNER JOIN course crs ON c.clubid = crs.clubid
       INNER JOIN subcourse subcrs ON crs.courseid = subcrs.courseid
       INNER JOIN tee t ON subcrs.subcourseid = t.subcourseid
       INNER JOIN par p ON t.parid = p.parid
       CROSS JOIN handicap h
 WHERE c.clubid = @clubid
0
 
LVL 12

Expert Comment

by:kselvia
ID: 20286762
For future reference, if you are using SQL 2005 Mgt Studio, you can select a query with the mouse, right click and choose Design In Query Editor and it will convert it to ANSI JOIN syntax for you.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question