SQL query to join two tables and get latest data based on ApprovedByLevel

Data Table

.

CREATE TABLE [dbo].[Data](
	[RecordNumber] [int] NULL,
	[Data1] [nvarchar](10) NULL,
	[Data2] [nvarchar](10) NULL
) ON [PRIMARY]

Approval Table

CREATE TABLE [dbo].[Approval](
	[RecordNumber] [int] NOT NULL,
	[ApprovedByLevel] [int] NOT NULL,
	[ApprovedByName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

<br/><br/>In approval table I store the record No. and the person who is approving the record. <br/>Many persons approve the same record and finally I would like to show only the Last Approved By Person Level and Name by combining the data table.<br/><br/>
Expecting query to Result

Using Ms-SQL Server 2014

> Update

In this

https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=d1c10b03c62b282d096a1f7d7dbfca4b

I want to get the result as

You can use row_number() window function to get the [ApprovedByLevel] that you want in the results:

select d.*, 
  COALESCE(a.[ApprovedByLevel], 0) AS [ApprovedByLevel], 
  a.[ApprovedByName]
from [Data] d left join (
  select *, 
    row_number() over (partition by [RecordNumber] order by [ApprovedByLevel] desc) rn
  from [Approval]
) a
on a.[RecordNumber] = d.[RecordNumber] and a.rn = 1

See the demo.<br/>
Results:

> RecordNumber | Data1 | Data2 | ApprovedByLevel | ApprovedByName
> -----------: | :---- | :---- | --------------: | :-------------
>         1001 | A     | AA    |               3 | Mr.C          
>         1002 | B     | BB    |               5 | Mr.DD
>         1003 | C     | CC    |               0 | 

I dont know if that helps, but you could use Join with Top 1 in sub select:

SELECT   *
FROM     dbo.data D
JOIN     dbo.Approval A
ON       A.ApprovedByLevel =
         (
         SELECT  TOP 1 ApprovedByLevel 
         FROM    Approval 
         WHERE   RecordNumber = D.RecordNumber
		 order by ApprovedByLevel desc
         )

> Note: You will have problems when there are more records with the same
> value in approvedlevel. You need a unique ID for the tabel
> dbo.approval. This should then be used in join condition.

If you have a RecordNumber in dbo.data but not in dbo.Approval, you could use left outer join:

SELECT   *
FROM     dbo.data D
LEFT OUTER JOIN dbo.Approval A
ON       A.ApprovedByLevel =
         (
         SELECT  TOP 1 ApprovedByLevel 
         FROM    Approval 
         WHERE   RecordNumber = D.RecordNumber
         order by ApprovedByLevel desc
         )

If you need Zero values instead of NULL you could use ISNULL()-Function:

SELECT   D.*, ISNULL(A.ApprovedByLevel,0), A.ApprovedByName
FROM     dbo.data D
LEFT OUTER JOIN dbo.Approval A
ON       A.ApprovedByLevel =
         (
         SELECT  TOP 1 ApprovedByLevel 
         FROM    Approval 
         WHERE   RecordNumber = D.RecordNumber
         order by ApprovedByLevel desc
         )

I would suggest you to add a date column ApprovingDate to avoid having problems when there are more records with the same value in ApprovedLevel :

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE [dbo].[Data](
    [RecordNumber] [int] NULL,
    [Data1] [nvarchar](10) NULL,
    [Data2] [nvarchar](10) NULL
)
INSERT INTO dbo.[Data]([RecordNumber],[Data1],[Data2])
VALUES(1001,'A','AA'),(1002,'B','BB')
CREATE TABLE [dbo].[Approval](
    [RecordNumber] [int] NOT NULL,
    [ApprovedByLevel] [int] NOT NULL,
    [ApprovedByName] [nvarchar](50) NOT NULL
) 
INSERT INTO [dbo].[Approval] VALUES(1001,1,'Mr.A'),(1001,2,'Mr.B'),
                            (1002,2,'Mr.AA'),(1001,3,'Mr.C'),
                            (1002,5,'Mr.DD')

Query 1:

select TOP 2 * from Approval as da 
inner join [data] as dd on da.recordnumber = dd.recordnumber 
order by da.approvedbylevel DESC

Query 2:

SELECT   *
FROM     dbo.data dt
JOIN     dbo.Approval a
ON       a.ApprovedByLevel =
         (
         SELECT  TOP 1 ApprovedByLevel 
         FROM    Approval 
         WHERE   RecordNumber = dt.RecordNumber
         order by ApprovedByLevel desc
         )

Results:

| RecordNumber | Data1 | Data2 | RecordNumber | ApprovedByLevel | ApprovedByName |
|--------------|-------|-------|--------------|-----------------|----------------|
|         1001 |     A |    AA |         1001 |               3 |           Mr.C |
|         1002 |     B |    BB |         1002 |               5 |          Mr.DD |