Query syntax

I have three tables which i have joined in as below.

This query returns many rows of data and is nearly what i want.

However how would i only get the rows where dbo.Documents.doc_id is at its maxium?


SELECT        dbo.Applications.app_id, dbo.Applications.app_XMSApplicationID, dbo.Applications.app_XMSApplicationName, dbo.Applications.app_Active, 
                     dbo.DocumentData.ddt_FieldName, dbo.DocumentData.ddt_FieldValue, dbo.Documents.doc_id
FROM          dbo.Applications
                     INNER JOIN dbo.Documents ON dbo.Applications.app_XMSApplicationID = dbo.Documents.doc_ApplicationID
                     INNER JOIN dbo.DocumentData ON dbo.Documents.doc_id = dbo.DocumentData.doc_id
					 where  dbo.Applications.app_XMSApplicationID = 62

Open in new window

soozhCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
I'm not sure I understand what you mean by where dbo.Documents.doc_id is at its maximum. Do you want the records only where the Doc_ID is the largest value - in other words only one doc_id?


Kelvin
0
Louis01Commented:
Try this?
SELECT a.app_id
     , a.app_XMSApplicationID
     , a.app_XMSApplicationName
     , a.app_Active
     , dd.ddt_FieldName
     , dd.ddt_FieldValue
     , d.doc_id
  FROM dbo.Applications a
        INNER JOIN dbo.Documents d
    ON a.app_XMSApplicationID = d.doc_ApplicationID
        INNER JOIN dbo.DocumentData dd
    ON d.doc_id = dd.doc_id
   AND EXISTS (select d1.doc_ApplicationID, MAX(d1.doc_id) as max_doc_id
                 from dbo.Documents d1
                group by d1.doc_ApplicationID
                where d1.doc_ApplicationID = d.doc_ApplicationID
               having d.doc_id = MAX(d1.doc_id))
 WHERE a.app_XMSApplicationID = 62

Open in new window

0
PortletPaulfreelancerCommented:
SELECT
       dbo.Applications.app_id
     , dbo.Applications.app_XMSApplicationID
     , dbo.Applications.app_XMSApplicationName
     , dbo.Applications.app_Active
     , dbo.DocumentData.ddt_FieldName
     , dbo.DocumentData.ddt_FieldValue
     , D.doc_id
FROM dbo.Applications
INNER JOIN (
             SELECT
                    doc_ApplicationID
                  , max(doc_id) AS doc_id
             FROM dbo.Documents
             GROUP BY
                    doc_ApplicationID
             ) AS D ON dbo.Applications.app_XMSApplicationID = D.doc_ApplicationID
INNER JOIN dbo.DocumentData ON D.doc_id = dbo.DocumentData.doc_id
WHERE dbo.Applications.app_XMSApplicationID = 62

Open in new window

nb: if there is a possibility that a document does not exist for any dbo.Applications.app_XMSApplicationID then the inner joins may have to be left joins.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Thanks! Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.