That's no really a Rails specific question. But I'll try to answer it anyway.
Unless you exclude the records in the payments table a left outer join will include all the relevant records. I don't know how this might work in MySQL, I've found that in some areas its support for complex SQL statements is quite limited, but you could try this;- select a.*,p.* from accounts a left outer join payments p on (a.id = p.account_id and p.id = (select max(p1.id) from payments p1 where p1.account_id = a.id)) You might need to put the sub-select into a where clause to get it working on MySQL. If that doesn't work you might try a stored procedure, or create a view which shows only the most recent payment for each account and then left outer join to the view. Cheers John Small Bharat Ruparel wrote: > I have to create a query from two tables. Table 1 is the accounts table > and table 2 is the payments table. Each account can have many payments. > I have to create a query that lists all accounts with the last payment. > If there are no payments then the query displays no information for the > payments but lists the account information anyway. If there are > multiple payments then I need to pick up the last payment. Example: > > accounts > id name > 1 a1 > 2 a2 > > payments > id account_id cents > 1 1 500 > 2 1 1000 > > if I run the following query: > > select a.id, a.name, p.id, p.cents from accounts left outer join > payments p where accounts.id = payments.account_id; then I get > a.id a.name p.id p.name > ------------------------------- > 1 a1 1 500 > 1 a1 2 1000 > 2 > ------------------------------- > > I would like to modify this query so that I get: > a.id a.name p.id p.name > ------------------------------- > 1 a1 2 1000 > 2 > ------------------------------- > > Appreciate your time in advance. > > Thanks. > > Bharat -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
