select contents from one table using another table

Question

I have two tables

A1	A2
a	2
b	3

A3	A4
a	row1
a	row 2
a	row 3
a	row 4
b	row 5
b	row 6
b	row 7
b	row 8
b	row 9

I want something like

A3	A4
a	row1
a	row 2
b	row 5
b	row 6
b	row 7

The second column in the first table should be the number of records i want from each group in the next table I tried where exists it did not work can you help me.!?

Answer 1

I would use ROW_NUMBER here on the second table. Then join to the first table and only retain records whose row number does not exceed the generated row number values.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY A3 ORDER BY A4) rn
    FROM Table2
)

SELECT t2.A3, t2.A4
FROM cte t2
INNER JOIN Table1 t1
    ON t2.A3 = t1.A1
WHERE
    t2.rn <= t1.A2;

Demo

Note that it would be ideal to have a more proper sequence column in the second table which determines the order for choosing records there.