Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert old style SQL to proper JOIN syntax

Posted on 2007-11-14
5
Medium Priority
?
704 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 1000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

971 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