Solved

Convert old style SQL to proper JOIN syntax

Posted on 2007-11-14
5
694 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
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 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