?
Solved

Outer Join not giving expected results.

Posted on 2006-04-27
9
Medium Priority
?
292 Views
Last Modified: 2006-11-18
I have a query to compare trades between months:
SELECT  mvp.COBDate,
      mvp.Position_ID,
      mvp.MarketValue, pri.MarketValue, pri.COBDate
FROM        tblArc_MarketValuePositions mvp LEFT OUTER JOIN tblArc_MarketValuePositions pri ON
      mvp.Position_ID = pri.Position_ID
WHERE       mvp.COBDate = @COB_Cur AND
      pri.COBDate = @COB_Pri AND
      mvp.CurrencyType_Identifier = 'USD'

I'm expecting 376 mvp records with some null values for the right hand table fields where theres no right hand position ID. What I get is only 292 rows as if I did an inner join. I'm not getting where I'm screwing up here. Any ideas?
0
Comment
Question by:stopher2475
  • 4
  • 3
  • 2
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16556877
what's the datatype of cobdate ? is it datetime datatype ? if so are you storing the time part there ?
And i want to know the same about @COB_Pri,@COB_Cur
0
 
LVL 2

Author Comment

by:stopher2475
ID: 16556893
Yep they're all datetime fields
0
 
LVL 5

Expert Comment

by:morisce
ID: 16556894
think in this condition :  pri.COBDate = @COB_Pri
it filters some records  
0
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.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16556934
WHERE      CONVERT(Varchar(10),mvp.COBDate,103) = CONVERT(Varchar(10),@COB_Cur,103)  AND
     CONVERT(Varchar(10),pri.COBDate,103) =CONVERT(Varchar(10),@COB_Pri,103)  AND
     mvp.CurrencyType_Identifier = 'USD'

0
 
LVL 2

Author Comment

by:stopher2475
ID: 16556965
Basically I'm doing a join on a table with itself. I need that condition so I only compare the 2 dates I want but I want to see where there's no match for the current date @COB_Cur with the prior date @COB_Pri.
I was looking for a way to avoid temporary tables. I thought I could do that by joining the table with itself.
0
 
LVL 5

Accepted Solution

by:
morisce earned 2000 total points
ID: 16557183
SELECT  mvp.COBDate,
     mvp.Position_ID,
     mvp.MarketValue, pri.MarketValue Pri_MarketValue, pri.COBDate Pri_COBDate
FROM       tblArc_MarketValuePositions mvp LEFT OUTER JOIN tblArc_MarketValuePositions pri ON
     mvp.Position_ID = pri.Position_ID
WHERE      mvp.COBDate = @COB_Cur AND
     pri.COBDate = @COB_Pri AND
     mvp.CurrencyType_Identifier = 'USD'
union
SELECT  mvp.COBDate,
     mvp.Position_ID,
     mvp.MarketValue, 0 Pri_MarketValue, @COB_Pri Pri_COBDate
FROM       tblArc_MarketValuePositions mvp  
WHERE      mvp.COBDate = @COB_Cur AND
     not exists (select 1 from tblArc_MarketValuePositions pri where
                       mvp.Position_ID = pri.Position_ID AND pri.COBDate = @COB_Pri) AND
     mvp.CurrencyType_Identifier = 'USD'
0
 
LVL 2

Author Comment

by:stopher2475
ID: 16557347
Hey that works. How come the outer join doesn't? Is it the table condition:  pri.COBDate = @COB_Pri ?
 
0
 
LVL 5

Expert Comment

by:morisce
ID: 16557478
perhaps, if you replace "pri.COBDate = @COB_Pri" with "isnull(pri.COBDate,@COB_Pri) = @COB_Pri" the result will be OK without adding the "union"
0
 
LVL 2

Author Comment

by:stopher2475
ID: 16557601
Still get the inner join set. I also tried      
(pri.COBDate = @COB_Pri OR pri.COBDate IS NULL) AND

I think I only have this problem when I'm using a single table.

Anyhow, thanks for the help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

864 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