CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Neo
Neo

Reputation: 16239

Why left join is not giving distinct result?

I have following sql query and my left join is not giving me distinct result please help me to trace out.

       SELECT  DISTINCT
                    Position.Date,
                    Position.SecurityId,
                    Position.PurchaseLotId,
                    Position.InPosition,
                    ISNULL(ClosingPrice.Bid, Position.Mark) AS  Mark
        FROM
                Fireball_Reporting.dbo.Reporting_DailyNAV_Pricing POSITION WITH (NOLOCK, READUNCOMMITTED)
                LEFT JOIN Fireball.dbo.AdditionalSecurityPrice ClosingPrice WITH (NOLOCK, READUNCOMMITTED) ON
                            ClosingPrice.SecurityID = Position.PricingSecurityID AND
                            ClosingPrice.Date = Position.Date AND
                            ClosingPrice.SecurityPriceSourceID = @SourceID AND
                            ClosingPrice.PortfolioID IN (5,6)  

 WHERE
        DatePurchased > @NewPositionDate AND
        Position.Date = @CurrentPositionDate AND
        InPosition = 1 AND

        Position.PortfolioId IN ( 
                                SELECT 
                                    PARAM 
                                FROM 
                                    Fireball_Reporting.dbo.ParseMultiValuedParameter(@PortfolioId, ',') 
                                ) AND
        (
        Position > 1 OR
        Position < - 1
        )

Now here in above my when I use LEFT JOIN ISNULL(ClosingPrice.Bid, Position.Mark) AS Mark and LEFT JOIN it is giving me more no of records with mutiple portfolio ids for e.g . (5,6)

If i put portfolioID =5 giving result as 120 records

If i put portfolioID =6 giving result as 20 records

When I put portfolioID = (5,6) it should give me 140 records but it is giving result as 350 records which is wrong . :(

It is happening because when I use LEFT JOIN there is no condition of PurchaseLotID in that as table Fireball.dbo.AdditionalSecurityPrice ClosingPrice not having column PurchaseLotID so it is giving me other records also whoes having same purchaseLotID's with diferent prices .

But I dont want that records

How can I eliminate those records ?

Upvotes: 0

Views: 256

Answers (2)

Augusto
Augusto

Reputation: 819

The most likely cause is that the left join produces duplicated PurchaseLotIds. The best way to know if if you perform a select distinct(PurchaseLotId) on your left side of the inner join.

Upvotes: 1

bummi
bummi

Reputation: 27385

You get one Entry per DailyLoanAndCashPosition.PurchaseLotId = NAVImpact.PurchaseLotId

which would mean you must have more entrys in with the same PurchaseLotId

Upvotes: 3

Related Questions