CODEWITHSUNDEEP
sqldataframe
Sinnclaire
Sinnclaire

Reputation: 29

How to get a limited results back from a list with information from another table

Create SQL Query that returns the even id, business id, user id, and transaction ID

There is a couple of requirements for this question:

Table one:

event_ID Business ID #_of_results_allowed
100 200 1
101 201 2
102 202 4
103 203 0

Table two:

date_ID event_id user_id transaction_ID
D12 100 300 zx1
D13 100 301 zx2
D14 100 302 zx3
D15 101 303 zx4
D16 102 333 zx5
D17 102 333 zx6
D18 102 302 zx7
D19 102 304 zx8
D20 102 333 zx9
D21 102 302 zx10
D22 103 304 zx11

What ive tried was:

Select t1.event_id, t1.business_id, t2.transaction_id, row_number() over(partition by t1.event_id order by t2.user_id) as row_number
From tableone t1
Join tabletwo t2 on t1.event_id = t2.event_id
Qualify row_number <= t1.#_of_results_allowed

but this did not help me with the second part and did not limit or order the user ID

Upvotes: 0

Views: 32

Answers (1)

eshirvana
eshirvana

Reputation: 24593

here is one way :

select t1.event_id, t1.business_id, t2.user_id, t2.transaction_id 
from table1 t1
join (
    select * , row_number() over (partition by event_id order by user_id) rn 
    from table2
) t2 on t1.event_id = t2.event_id and t2.rn <= _of_results_allowed

Upvotes: 0

Related Questions