December 13, 2012 at 7:38 am
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 ?
December 13, 2012 at 7:55 am
I don't have access to your database, so all I can say here is that the query is doing what you're telling it to. You just have a different number of matching records between the tables than what you think you have, based on the join criteria you are using.
Without actually being able to look at the data in your tables, there's no real way to determine exactly what's going on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 8:06 am
Why do you have NOLOCK and READUNCOMMITTED? You are using two query hints that do the same thing. Do you really need/want to use this hint? Are you aware of the issues surrounding those query hints?
Not sure what your ParseMultiValuedParameter iTFV is doing but if it looping I might suggest reading the article in my signature about splitting strings. In there you will find a super fast iTFV for this type of parsing.
To help with your actual question, you need to provide more details as G2 already said. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2012 at 9:47 am
I agree with GSquared - without access to your data or at least a sanitized test copy thereof, determing a cause could be difficult if not impossible. However, having looked at this, it appears that it MIGHT be a case of adding
AND Position.PortfolioID = ClosingPrice.PortfolioID
to your JOIN condition. I base this idea on your statements that indicate a single value for PortfolioID provides a correct answer, while a multiple value scenario does not. I suspect the LEFT JOIN causes the two differing PortfolioID's to create non-unique records because a record with a Position.PortfolioID of 5 could match a record with a ClosingPrice.PortfolioID of 6, given your existing JOIN and your existing WHERE clause. I could easily be wrong, and there could be another factor I can't see because I don't have any data to work with. Let us know...
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
December 13, 2012 at 11:07 am
Hi,
I'm very thankful to you
sgmunson and others too
You gave me the right clue PortfolioId's need to be matched in too more LEFT JOINS as it is LEFT JOIN it was not working correctly as per expectation .
Thanks 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply