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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---