[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

nested/sub query in sql

Posted on 2013-11-07
2
Medium Priority
?
303 Views
Last Modified: 2013-11-07
Hi I have two tables, one called vwApplicationCurrent and one called vwApplicationCategoryCurrent.  The tables are a one-many relationship based on Ref no.  There can be many Categories assigned to one Application.

I am trying to find the most recent Application Category (would be the row with the max CatAllocationDate) and return the Category Code along with other fields as shown below.

I am a newbie to SQL and am thinking this should be simple but just can't get it.   Here is the sample bit of data

TABLENAME vwApplicationCurrent            
RefNo      ApplicationDate      CurrentApplication
2063      31/10/2013      Yes
            
            
TABLENAME vwApplicationCategoryCurrent            
RefNo      CatCode      CatAllocationDate
2063      BN      25/08/2013
2063      NO      26/08/2013
2063      ME      27/08/2013


I am therefore looking to return the following from my view/sql statement:
RefNo       ApplicationDate    CatCode    CatAllocationDate    CurentApplication
2063         31/10/2013           ME               27/08/2013               Yes

Many thanks in advance.
0
Comment
Question by:deborahhowson00
2 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 2000 total points
ID: 39629650
select ac.RefNo, ac.ApplicationDate, acc1.CatCode, acc1.CatAllocationDate, ac.CurrentApplication
  from vwApplicationCurrent ac
        left outer join vwApplicationCategoryCurrent acc1
    on ac.RefNo = acc1.RefNo
   and acc1.CatAllocationDate = (select MAX(acc2.CatAllocationDate) 
                                  from vwApplicationCategoryCurrent acc2
                                 where acc1.RefNo = acc2.RefNo)

Open in new window

0
 

Author Closing Comment

by:deborahhowson00
ID: 39629741
Super thank you very much! :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

834 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