LIMITing an SQL JOIN

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

You'll have to specify which expense item you want to get. The most expensive? The newest? Then join against a subquery that returns only that:

SELECT    expense.*, transaction.*, user.*FROM    (SELECT * FROM expense WHERE ...) AS expenseINNER JOIN    transaction ON expense_id = transaction_expense_id

Since upgrading the SQL server is not an option, I may end up doing two queries.

expenses = SELECT * FROM expense ... LIMIT xforeach expenses as expense    expense.transactions = SELECT * FROM transacion WHERE transaction_expense_id = expense.expense_id