Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert old style SQL to proper JOIN syntax

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

704 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