Solved

SQL Query syntax help

Posted on 2015-02-13
9
72 Views
Last Modified: 2015-02-18
Hello all,

I have the following query:

select MV.MVDate, DE.DataEntryID,
            CU.ISOCurrencyCode
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

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?
0
Comment
Question by:sbornstein2
9 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40607876
Depends on which note do you want. The first one? The last one?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40607878
with myTable as (
select MV.MVDate, DE.DataEntryID,
            CU.ISOCurrencyCode
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
)
Select top(1) Note
From docNote as D
inner join myTable as M on D.InstanceID = M.DataEntryID
where D.EntityID = 9
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 40607917
Just taking what Phillip did, and switching some things around:

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

Open in new window


Now you just have to tweak the CTE query to get the note that you really want.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40608238
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
cross apply (
    select top (1) Note
    from [Customer].[dbo].[docNote]
    where
         EntityID = 9 and
         InstanceID = DE.DataEntryID
    order by NoteID --desc
) as dn
0
 

Author Comment

by:sbornstein2
ID: 40608365
I like the cross apply but it does not bring back records if there is no note.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40608369
OOPS, sorry, quite right, I should have used
OUTER APPLY
rather than CA.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40608371
...
outer apply (
     select top (1) Note
     from [Customer].[dbo].[docNote]
     where
          EntityID = 9 and
          InstanceID = DE.DataEntryID
     order by NoteID --desc
 ) as dn
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40609027
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
;

Open in new window

0
 

Author Closing Comment

by:sbornstein2
ID: 40617752
this worked perfect and nice and clean thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question