SQL JOIN syntax

Question

Post reply

SQL JOIN syntax

Chandu-212374

SSCrazy

Points: 2247
More actions
November 11, 2007 at 9:33 pm

#119462

Can someone please provide me the LEFT/RIGHT OUTER JOIN syntax for the following query..
SELECT *
FROM tbl_PARS_jobStatus js,
tbl_PARS_rptOrgParams org,
tbl_PARS_Org1 o1,
tbl_PARS_Org2 o2,
tbl_PARS_Org3 o3,
tbl_PARS_Org4 o4
where org.strjobname=@strJobName
and org.strJobName = js.strJobName
and org.intOrg1Id *= o1.intOrgId
and js.intLanguage *= o1.intLanguage
and org.intOrg2Id *= o2.intOrgId
AND js.intLanguage *= o2.intLanguage
and org.intOrg3Id *= o3.intOrgId
AND js.intLanguage *= o3.intLanguage
and org.intOrg4Id *= o4.intOrgId
AND js.intLanguage *= o4.intLanguage
and org.intOrg1Id <> 0

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

*= means LEFT OUTER JOIN

in general you can replace *= like so:

SELECT *

FROM Orders, Customers

ON Orders.CustomerID *= Customers.CustomerID

becomes:

SELECT *

FROM Orders

LEFT JOIN Customers

ON Orders.CustomerID = Customers.CustomerID

So wherever you have *=, you'll move a LEFT JOIN ... ON ... to the FROM section. Start by moving one of the joins at a time and appending to the FROM section and you'll work it through.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

And, please don't post duplicate posts.... we'll find ya...

http://qa.sqlservercentral.com/Forums/Topic420902-145-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 3

Enterprise Manager would be happy to rewrite your old-style joins. In the Tables view of any SQL 2000 database, right-click on a table and select "Open table -> Query". Then replace the text in the SQL pane with your query and it will be reformatted as soon as you click on a different pane.

I tried this with your query and it complained about the variable, so I just put quotes around it. This is the result:

SELECT *

FROM tbl_PARS_jobStatus js INNER JOIN

tbl_PARS_rptOrgParams org ON js.strJobName = org.strJobName LEFT OUTER JOIN

tbl_PARS_Org1 o1 ON org.intOrg1Id = o1.intOrgId AND js.intLanguage = o1.intLanguage LEFT OUTER JOIN

tbl_PARS_Org2 o2 ON org.intOrg2Id = o2.intOrgId AND js.intLanguage = o2.intLanguage LEFT OUTER JOIN

tbl_PARS_Org3 o3 ON org.intOrg3Id = o3.intOrgId AND js.intLanguage = o3.intLanguage LEFT OUTER JOIN

tbl_PARS_Org4 o4 ON org.intOrg4Id = o4.intOrgId AND js.intLanguage = o4.intLanguage

WHERE (org.strjobname = '@strJobName') AND (org.intOrg1Id <> 0)

I hate the way it formats the joins, but at least all the *= operators are gone.