Reputation: 717
There are 2 tables queries
and transactions
, 1 transaction can have multiple queries. I want to fetch the list of queries for 10 transactions. I tried this query but this is limiting total row count.
SELECT t.tid
, q.id
, q.timestamp
, q.domain
, q.health
, q.alexa_rank
, q.destination
, t.age
, t.sb_isValid
, t.userResponse
, t.suggestion
from queries q
join transactions t
on q.id like concat(t.tid, '%')
where t.uid = '115800979895438175088'
order
by t.time DESC
, tid
limit 10
This query is returning 10 rows including queries and transactions, but I want 10 transactions with its queries.
If there are 10 transactions and each has 3 queries then it should return 30+10 = 40 rows but it is returning 10 rows only.
Upvotes: 0
Views: 1817
Reputation: 95101
Select the ten transactions in a subquery:
select *
from
(
select *
from transactions
order by time desc
limit 10
) t
join queries q on q.id like concat(t.tid, '%')
order by t.time desc, t.tid, q.timestamp desc;
Upvotes: 2