CODEWITHSUNDEEP
sqlsql-serverjoinsql-server-2008-r2full-outer-join
TJ_
TJ_

Reputation: 255

SQL Server 2008R2 Full outer join not working

I am trying to run the below query 

SELECT      mr.AsofDate as date,
            mr.FA,
            mr.TPNL as tpnl,
            mr.MPNL as mpnl,
            mrf.tpnl as mrfTpnl,
            mrf.cpnl as mrfCpnl
FROM        vw_daily mr
FULL        OUTER JOIN mrfeeddaily  mrf
ON          mr.FA = mrf.book and mr.AsofDate = mrf.AsOfDate
WHERE       mr.AsofDate = '20141121'

But i end up getting only rows from the first View vw_daily and the columns from mrfeeddaily are NULL, doesn't Full join return all non matching rows as well ? what am i missing.

There is no common data between the view and the table.

Upvotes: 3

Views: 811

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Move the filter to ON condition

ON tells what are the rows to used for Joining but where clause filters the result of join.

Only matched rows in vw_daily table is going to have value in asofdate so the filtering the rows in Where clause is going to do implicit conversion from FULL OUTER JOIN to INNER JOIN

SELECT mr.asofdate AS date, 
       mr.fa, 
       mr.tpnl     AS tpnl, 
       mr.mpnl     AS mpnl, 
       mrf.tpnl    AS mrfTpnl, 
       mrf.cpnl    AS mrfCpnl 
FROM   vw_daily mr 
       FULL OUTER JOIN mrfeeddaily mrf 
                    ON mr.fa = mrf.book 
                       AND mr.asofdate = mrf.asofdate 
                       AND mr.asofdate = '20141121'

Upvotes: 3

Matt
Matt

Reputation: 15061

Filter the condition in your join.

SELECT mr.AsofDate AS date, mr.FA, mr.TPNL AS tpnl, mr.MPNL AS mpnl,  mrf.tpnl AS mrfTpnl, mrf.cpnl AS mrfCpnl
FROM vw_daily mr
FULL OUTER JOIN mrfeeddaily  mrf ON mr.FA = mrf.book 
AND mr.AsofDate = mrf.AsOfDate 
AND mr.AsofDate = '20141121'

Upvotes: 0

Related Questions