Outer Join not giving expected results.

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?
LVL 2
stopher2475Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
stopher2475Author Commented:
Yep they're all datetime fields
0
 
morisceCommented:
think in this condition :  pri.COBDate = @COB_Pri
it filters some records  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
stopher2475Author Commented:
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
 
morisceCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
stopher2475Author Commented:
Hey that works. How come the outer join doesn't? Is it the table condition:  pri.COBDate = @COB_Pri ?
 
0
 
morisceCommented:
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
 
stopher2475Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.