[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Changing the non-ANSI outer join operators ("*=" or "=*") to left or right outer Joins

Posted on 2006-05-12
5
Medium Priority
?
2,064 Views
Last Modified: 2012-06-27
I have a SQL Stored Procedure which is basically shown below.  I need to change it so it uses Right or Left Outer Join operators instead of the non-ANSI outer join operators.  I'm assuming if you know the answer you know what i'm talking about.  I can elaborate if needed.

I tried, but was uncessesful with my beginner knowledge and unfortunately have a short timeline.

thanks!
Christa


DECLARE @StartDate DATETIME
Set @StartDate = GETDATE()
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(second,-1,DATEADD(month,1,@StartDate))


SELECT TOP 50
      A.AccountID,
      Username,
      PortraitID,
      SUM(Points) AS Points,
      SUM(Heads) AS Heads,
      SUM(CASE WHEN Stakes = 4 THEN Heads ELSE 0 END) AS USD100_Heads,
      SUM(CASE WHEN Stakes = 3 THEN Heads ELSE 0 END) AS USD30_Heads,
      SUM(CASE WHEN Stakes = 2 THEN Heads ELSE 0 END) AS USD20_Heads,
      SUM(CASE WHEN Stakes = 1 THEN Heads ELSE 0 END) AS USD10_Heads,
      SUM(CASE WHEN Stakes = 0 THEN Heads ELSE 0 END) AS USD5_Heads
FROM
      HeadhunterLeaderboardPoints HHLBP,
      HeadhunterLeaderboardHeads HHLBH,
      Account A,
      AccountPreferences AP
WHERE
      HHLBP.AccountID = A.AccountID
      AND HHLBP.AccountID *= AP.AccountID
      AND (HHLBP.AccountID *= HHLBH.AccountID AND HHLBP.TournamentID *= HHLBH.TournamentID)
      AND HHLBP.TournamentStartDate BETWEEN @StartDate AND @EndDate
      AND HHLBH.TournamentStartDate BETWEEN @StartDate AND @EndDate
GROUP BY
      A.AccountID,
      Username,
      PortraitID
ORDER BY SUM(Points) DESC
0
Comment
Question by:christamcc
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 16672345
Try it this way:

DECLARE @StartDate DATETIME
Set @StartDate = GETDATE()
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(second,-1,DATEADD(month,1,@StartDate))


SELECT      TOP 50
      A.AccountID,
      Username,
      PortraitID,
      SUM(Points) AS Points,
      SUM(Heads) AS Heads,
      SUM(CASE WHEN Stakes = 4 THEN Heads ELSE 0 END) AS USD100_Heads,
      SUM(CASE WHEN Stakes = 3 THEN Heads ELSE 0 END) AS USD30_Heads,
      SUM(CASE WHEN Stakes = 2 THEN Heads ELSE 0 END) AS USD20_Heads,
      SUM(CASE WHEN Stakes = 1 THEN Heads ELSE 0 END) AS USD10_Heads,
      SUM(CASE WHEN Stakes = 0 THEN Heads ELSE 0 END) AS USD5_Heads
From      HeadhunterLeaderboardPoints HHLBP
      Left Join HeadhunterLeaderboardHeads HHLBH On HHLBP.AccountID = HHLBH.AccountID AND HHLBP.TournamentID = HHLBH.TournamentID
      Inner Join Account A On HHLBP.AccountID = A.AccountID
      Left Join AccountPreferences AP On HHLBP.AccountID = AP.AccountID
WHERE      HHLBP.TournamentStartDate BETWEEN @StartDate AND @EndDate
      AND HHLBH.TournamentStartDate BETWEEN @StartDate AND @EndDate
GROUP BY
      A.AccountID,
      Username,
      PortraitID
ORDER BY SUM(Points) DESC
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16672352
Also, the following question is now considered abandoned, please attend to it:
http://www.experts-exchange.com/Web/WebDevSoftware/Flash/Q_21790646.html
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16680599
to ensure that you Get the results you desire...

consider usually writting it like this with Subqueries
  (particularly when you want to further restrict the selection for  any of the outer joined tables... ,<where conditions>...)


DECLARE @StartDate DATETIME
Set @StartDate = GETDATE()
DECLARE @EndDate DATETIME
SET @EndDate = DATEADD(second,-1,DATEADD(month,1,@StartDate))


SELECT TOP 50
     A.AccountID,
     Username,
     PortraitID,
     SUM(Points) AS Points,
     SUM(Heads) AS Heads,
     SUM(CASE stakes when 4 THEN Heads ELSE 0 END) AS USD100_Heads,
     SUM(CASE stakes WHEN 3 THEN Heads ELSE 0 END) AS USD30_Heads,
     SUM(CASE stakes WHEN 2 THEN Heads ELSE 0 END) AS USD20_Heads,
     SUM(CASE stakes WHEN 1 THEN Heads ELSE 0 END) AS USD10_Heads,
     SUM(CASE stakes WHEN 0 THEN Heads ELSE 0 END) AS USD5_Heads

 FROM Account as A
Inner Join
      (select *
         from HeadhunterLeaderboardPoints
        Where TournamentStartDate BETWEEN @StartDate AND @EndDate
      ) as HHLBP
   on HHLBP.AccountID = A.AccountID
 Left Outer Join
      (Select *
         from HeadhunterLeaderboardHeads
        Where TournamentStartDate BETWEEN @StartDate AND @EndDate
      ) as HHLBH
   on HHLBP.AccountID = HHLBH.AccountID
  AND HHLBP.TournamentID = HHLBH.TournamentID
 Left Outer Join AccountPreferences as AP
   on HHLBP.AccountID = AP.AccountID

GROUP BY
     A.AccountID,
     Username,
     PortraitID

ORDER BY 4 DESC
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16682368

*=
left outer join

=*
right outer join
0
 

Author Comment

by:christamcc
ID: 16685653
Thanks acperkins.  That's what I was looking for!

Lowfatspread, I appreciated your input too.  For some I've been advised against using subqueries. (?)  It's good for me to see it both ways though.  Helps with my learning.

... and thanks for the general info imran_fast.  I had gotten that far, but it was the piecing it all together where I got stuck.  That little pearl of info is good to know though!  I sure it will help someone.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

834 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