CODEWITHSUNDEEP
mysqlsql
Pritam Pawade
Pritam Pawade

Reputation: 717

Inner join limit the rows from second table

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

Answers (1)

Thorsten Kettner
Thorsten Kettner

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

Related Questions