select contents from one table using another table

user_15571001 0
Viewed 33

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.!?

subquery select tsql sql
2 Answers

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;

screen capture from demo link below

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.

user_18632290 
 declare @temp1 as table(A1 varchar(1),A2 varchar(1))
 declare @temp2 as table(A3 varchar(1),A4 varchar(10));

 insert into @temp1(A1,A2) values('a',2),('b',3)
 insert into @temp2(A3,A4) values('a','row 1'),
 ('a',  'row 2'),
 ('a',  'row 3'),
 ('a',  'row 4'),
 ('b',  'row 5'),
 ('b',  'row 6'),
 ('b',  'row 7'),
 ('b',  'row 8'),
 ('b',  'row 9')

 select A1,A4 
 from
 (
 select 
 tmp1.A1,tmp1.A2,tmp2.A4,
 Row_Number() over(partition by tmp1.A1 order by tmp1.A1) RowID
 from @temp1 tmp1
 join @temp2 tmp2 on
    tmp1.A1=tmp2.A3
 )x
 where RowID<=A2

output:

A1  A4
a   row 1
a   row 2
b   row 5
b   row 6
b   row 7
user_40011770
Related