29

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?

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

BenMorel
  • 30,280
  • 40
  • 163
  • 285
Thomas R
  • 2,986
  • 5
  • 29
  • 31

4 Answers4

16

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

Ben
  • 59,328
  • 35
  • 82
  • 105
  • "#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" Server version: 5.0.27-community-nt – Thomas R Jan 30 '09 at 09:49
  • 1
    Subquery is the Right Way to do this. You've hit a MySQL limitation; either upgrade (to 5.1, which might fix this, or to Postgres). Otherwise you'll have to consider a non-pure SQL solution or something really hideous. – kquinn Jan 30 '09 at 09:52
  • Marking as accepted answer because it would work in another SQL environment. – Thomas R Jan 30 '09 at 09:57
  • @rixth: Thanks. I've also added another possible solution that might work for you... – Ben Jan 30 '09 at 09:59
  • :-) That's good news! BTW: This might be entirely unhelpful, but I wonder if you might want to consider renaming your columns in your tables? If you renamed expense_id to just id then you could refer to it as expense.id or just id (when it's unambiguous) instead. It may help make things easier... – Ben Jan 30 '09 at 10:06
9

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 expense
INNER JOIN
    transaction ON expense_id = transaction_expense_id
David Schmitt
  • 54,766
  • 26
  • 117
  • 159
2

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

expenses = SELECT * FROM expense ... LIMIT x
foreach expenses as expense
    expense.transactions = SELECT * FROM transacion WHERE transaction_expense_id = expense.expense_id
Thomas R
  • 2,986
  • 5
  • 29
  • 31
0

Even though this is old... came across this in a search and thought I'd add a couple thoughts, given the inability to use limit in a subquery... 

select e.expense_id, transaction.* 
from (select max(expense_id) from expense) e 
    inner join transaction t ON e.expense_id = t.transaction_expense_id

or if you want to have all columns from expense and not just expense_id you could nest subqueries.

select e.*, t.*
from (
     select e1.*
     from expense e1 inner join (select max(expense_id) from expense) e2
        on e1.expense_id = e2.expense_id
     ) e  inner join transaction t on e.expense_id = t.transaction_expense_id
jschicago
  • 11
  • 3