SQL Server : matching records from 2 tables with sorting
<p>Let’s assume I have two tables, A and B, both with an ID column and a foreign key (value).</p>

<p>I want to do a select based query that returns only the matching records, not including those that don't meet the condition of having the same data (<code>ID</code> and <code>Value</code> columns), also sorted by <code>Value</code> column of table B. </p>

<p>Table A</p>

<pre><code>SELECT *
FROM (VALUES
(15, 1),
(16, 2),
(17, 3)
) as t(idMetadata, [Value])
</code></pre>

<p>Table B</p>

<pre><code>SELECT *
FROM (VALUES
(185442, 22008, 16, 6 ,2),
(187778, 22269, 16, 6 ,2),
(211260, 24925, 16, 6 ,2),
(251476, 29431, 15, 4 ,1),
(251477, 29431, 16, 5 ,2),
(251478, 29431, 17, 6 ,3)
) as t(idDet, idEnc, idMetadata, OrderValue, [Value])
</code></pre>

<p>The expected result is</p>

<p><a href="https://i.stack.imgur.com/Bhal2.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Bhal2.jpg" alt="enter image description here"></a></p>

<p>Can this be achieved by a single query? Or do I have to create a CTE or subqueries ?</p>

<p>EDIT: Sorry, I forgot to mention another condition for the query: in Table B, the records should have the same  idEnc and the OrderValue column should be consecutive, That's why the expected result also have same idEnc and the OrderValue is 4, 5 &amp; 6.</p>