sql - Get 3 most visiting customers from table T1 and get top 5 products each one of them buy from table T2 -

sql - Get 3 most visiting customers from table T1 and get top 5 products each one of them buy from table T2 -

i need fetch 3 visiting customers table t1 , , top 5 products each 1 of them purchase table t2.

table t1 has kirk visited 3 times, hulk 3 times, john 2 times .. these 3 should fetched only. thor , rndm guy visited 1 time r left top 3 fetched

table 1 (t1) ----------------------------------- name age visitor code ----------------------------------- kirk 18 1285 thor 99 1284 kirk 18 1285 hulk 19 1286 kirk 18 1285 john 19 0007 hulk 19 1286 john 19 0007 hulk 19 1286 rndm 19 0008 table 2 (t2) ----------------------------------- product mrp visitor code --example kirk top 3 prods d c. ----------------------------------- 111 1285 111 1285 111 1285 b 191 1285 c 192 1285 c 192 1285 d 190 1285 d 190 1285 d 190 1285 d 190 1285

what tried :

select t2.product , count(*) x t1,t2 t2.visitor code = (select t1.visitor code , count(*) x t1,t2 grouping t1.visitor code order x desc fetch first 3 rows only) grouping t1.visitor code order x desc

the problem is: need t1.visitor code sub query match t2 table's t2.visitor code. have used count in t1 table sub query.

output result:

name product visitor code kirk 1285 kirk d 1285 kirk c 1285 hulk c 1286 hulk 1286 hulk b 1286 -- , john , 3 products

you can utilize derived tables solve query. first derived table contains top 3 visitors, joined 2nd derived table contains top 5 products each visitor.

select t1.*, t2.* (select name, age, visitor_code, count(*) visit_count table1t t1 grouping visitor_code order visit_count desc limit 3) t1 bring together ( select product, mrp, visitor_code, rank() on (partition t3.visitor_code order t3.product_count desc) rank ( select product, mrp, visitor_code, count(*) product_count table2 grouping product, mrp, visitor_code ) t3 rank <= 5 ) t2 on t1.visitor_code = t2.visitor_code

sql db2