sbornstein2
asked on
SQL Query syntax help
Hello all,
I have the following query:
select MV.MVDate, DE.DataEntryID,
CU.ISOCurrencyCode
from [Customer].[snp].[MarketVa lueSnap] MV
inner join [Customer].[dbo].[cbiCurre ncy] CU ON MV.CurrencyID = CU.CurrencyID
inner join [Customer].[dbo].[cbiDataE ntry] DE ON MV.DataEntryID = DE.DataEntryID
I then have a table called: docNote
In this table it has the following fields:
NoteID,
EntityID,
InstanceID,
Note
What I need to do somehow is get the Note from this table where EntityID = 9 (DataEntry) and InstanceID = DE.DataEntryID from my query. I only want to get the 1st note just in case there is more than one to protect myself. What is the best way to handle this?
I have the following query:
select MV.MVDate, DE.DataEntryID,
CU.ISOCurrencyCode
from [Customer].[snp].[MarketVa
inner join [Customer].[dbo].[cbiCurre
inner join [Customer].[dbo].[cbiDataE
I then have a table called: docNote
In this table it has the following fields:
NoteID,
EntityID,
InstanceID,
Note
What I need to do somehow is get the Note from this table where EntityID = 9 (DataEntry) and InstanceID = DE.DataEntryID from my query. I only want to get the 1st note just in case there is more than one to protect myself. What is the best way to handle this?
Depends on which note do you want. The first one? The last one?
with myTable as (
select MV.MVDate, DE.DataEntryID,
CU.ISOCurrencyCode
from [Customer].[snp].[MarketVa lueSnap] MV
inner join [Customer].[dbo].[cbiCurre ncy] CU ON MV.CurrencyID = CU.CurrencyID
inner join [Customer].[dbo].[cbiDataE ntry] DE ON MV.DataEntryID = DE.DataEntryID
)
Select top(1) Note
From docNote as D
inner join myTable as M on D.InstanceID = M.DataEntryID
where D.EntityID = 9
select MV.MVDate, DE.DataEntryID,
CU.ISOCurrencyCode
from [Customer].[snp].[MarketVa
inner join [Customer].[dbo].[cbiCurre
inner join [Customer].[dbo].[cbiDataE
)
Select top(1) Note
From docNote as D
inner join myTable as M on D.InstanceID = M.DataEntryID
where D.EntityID = 9
Just taking what Phillip did, and switching some things around:
Now you just have to tweak the CTE query to get the note that you really want.
with myTable as (
Select D.EntityID, MAX(Note) AS Note
From docNote as D
where D.EntityID = 9
group by D.EntityID
)
select MV.MVDate, DE.DataEntryID,
CU.ISOCurrencyCode,
myTable.Note
from [Customer].[snp].[MarketValueSnap] MV
inner join [Customer].[dbo].[cbiCurrency] CU ON MV.CurrencyID = CU.CurrencyID
inner join [Customer].[dbo].[cbiDataEntry] DE ON MV.DataEntryID = DE.DataEntryID
inner join myTable ON DE.DataEntryID = myTable.EntityID
Now you just have to tweak the CTE query to get the note that you really want.
select MV.MVDate, DE.DataEntryID,
CU.ISOCurrencyCode,
dn.Note
from [Customer].[snp].[MarketVa lueSnap] MV
inner join [Customer].[dbo].[cbiCurre ncy] CU ON MV.CurrencyID = CU.CurrencyID
inner join [Customer].[dbo].[cbiDataE ntry] DE ON MV.DataEntryID = DE.DataEntryID
cross apply (
select top (1) Note
from [Customer].[dbo].[docNote]
where
EntityID = 9 and
InstanceID = DE.DataEntryID
order by NoteID --desc
) as dn
CU.ISOCurrencyCode,
dn.Note
from [Customer].[snp].[MarketVa
inner join [Customer].[dbo].[cbiCurre
inner join [Customer].[dbo].[cbiDataE
cross apply (
select top (1) Note
from [Customer].[dbo].[docNote]
where
EntityID = 9 and
InstanceID = DE.DataEntryID
order by NoteID --desc
) as dn
ASKER
I like the cross apply but it does not bring back records if there is no note.
OOPS, sorry, quite right, I should have used
OUTER APPLY
rather than CA.
OUTER APPLY
rather than CA.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
perhaps the use of a derived table using row_number() to get the latest note
SELECT
MV.MVDate
, DE.DataEntryID
, CU.ISOCurrencyCode
, DN.Note
FROM [Customer].[snp].[MarketValueSnap] MV
INNER JOIN [Customer].[dbo].[cbiCurrency] CU ON MV.CurrencyID = CU.CurrencyID
INNER JOIN [Customer].[dbo].[cbiDataEntry] DE ON MV.DataEntryID = DE.DataEntryID
LEFT JOIN (
SELECT
InstanceID
, Note
, ROW_NUMBER() OVER (PARTITION BY InstanceID
ORDER BY NoteID DESC /* for latest */) AS RN
FROM [Customer].[dbo].[docNote]
WHERE EntityID = 9
) AS DN ON DE.DataEntryID = DN.InstanceID AND DN.RN = 1
;
ASKER
this worked perfect and nice and clean thanks