May 7, 2012 at 7:54 am
I need to put where condition inside NOT EXISTS clause in sql.
In below need to check the duplicate records for that in below sql query i need to put Date='2012-05-07' and SecurityId= '52211' but problem is inner join is used and i'm new bie not getting how to put these where clause please help.
SELECT DISTINCT
BondPrice.SecurityPriceId
FROM
dbo.Reporting_BondIndicative Reporting_BondIndicative
INNER JOIN
dbo.BondPrice BondPrice ON
Reporting_BondIndicative.SecurityId = BondPrice.SecurityId AND
BondPrice.SecurityPriceSourceId = @SecurityPriceSourceComposite
WHERE
BondPrice.Date = @Date AND
NOT EXISTS
(
SELECT
'z'
FROM
dbo.Reporting_BondPrices
WHERE
Reporting_BondPrices.SecurityId = BondPrice.SecurityId AND
Reporting_BondPrices.Date = BondPrice.Date
)
any need of 'NOT EXISTS' section or directly canput condition in where clause?
May 7, 2012 at 9:13 am
I can't understand what you are asking here. Can you try to explain your issue more clearly?
_______________________________________________________________
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/
May 7, 2012 at 11:32 am
Sean Lange (5/7/2012)
I can't understand what you are asking here. Can you try to explain your issue more clearly?
+1
Please provide some sample data, along with expected results, which may make things clearer.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2012 at 11:50 am
ashuthinks (5/7/2012)
I need to put where condition inside NOT EXISTS clause in sql.In below need to check the duplicate records for that in below sql query i need to put Date='2012-05-07' and SecurityId= '52211' but problem is inner join is used and i'm new bie not getting how to put these where clause please help.
SELECT DISTINCT
BondPrice.SecurityPriceId
FROM
dbo.Reporting_BondIndicative Reporting_BondIndicative
INNER JOIN
dbo.BondPrice BondPrice ON
Reporting_BondIndicative.SecurityId = BondPrice.SecurityId AND
BondPrice.SecurityPriceSourceId = @SecurityPriceSourceComposite
WHERE
BondPrice.Date = @Date AND
NOT EXISTS
(
SELECT
'z'
FROM
dbo.Reporting_BondPrices
WHERE
Reporting_BondPrices.SecurityId = BondPrice.SecurityId AND
Reporting_BondPrices.Date = BondPrice.Date
)
any need of 'NOT EXISTS' section or directly canput condition in where clause?
Here's what this query does. It gets the distinct list of Bond Price Security ID's where the Bond Prices are in the Reporting_BondIndicative table and not in the Reporting_BondPrices table. Where the Security Price Source ID exists and it matches a date.
I think what you need is to add it to the outer query WHERE clause. The EXISTS Simply is filtering the select by any BondPrice that is not in Reporting_BondPrices by Date and ID.
observation, since you are using an INNER JOIN you might want to move the second part of your JOIN criteria (SecurityPriceSourceID = @SecurityPriceSourceComposite) to the WHERE Clause.
I might recommend that you shorten your aliases to make it somewhat clearer (as the below)
SELECT
DISTINCT
bp.SecurityPriceId
FROM
dbo.Reporting_BondIndicative rbi
INNER JOIN
dbo.BondPrice bp ON
rbi.SecurityId = bp.SecurityId AND
bp.SecurityPriceSourceId = @SecurityPriceSourceComposite
WHERE
bp.SecurityId = '52211' AND
bp.Date = @Date AND
NOT EXISTS
(
SELECT
'z'
FROM
dbo.Reporting_BondPrices rbp
WHERE
rbp.SecurityId = bp.SecurityId AND
rbp.Date = bp.Date
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply