Search code examples
mysqlsqldatabasejoinsql-limit

How to limit returned results of a JOIN query in MySQL

I am trying to limit the following SQL statement.

SELECT expense.*, transaction.* FROM expense
INNER JOIN transaction ON expense_id = transaction_expense_id

What I want to do, is limit the number of 'parent' rows. IE. if I do a LIMIT 1, I would receive only one expense item, but still get all transactions associated with it.

How would this be achieved? I am using MySQL 5.0

At this stage, if I do LIMIT 1, I get one expense, and only one transaction.

Solution

  • So assuming we can exclude the user table, it could be rewritten as:

    select * from expense, transaction where expense_id = transaction_expense_id

    Now if you want to apply a limit, you could do it like this:

    select * from expense, transaction where expense_id = transaction_expense_id and 
  expense_id in (select expense_id from expense limit 1)

    Would that do what you wanted? Obviously you need to be cautious about what order your expense_ids are going to come back in, so you probably want to use ORDER BY whatever.

    Edit: Given the MySQL limitation described in your comment below, maybe this will work:

    select * from (select id from expense order by WHATEVER limit 1) as t1, transaction where expense_id=transaction_expense_id;

    Ben