Forum: Ruby on Rails Representing left outer join in MySQL and Rails

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
D3fc5887a2f39f2e0c8989d39ce5e6f9?d=identicon&s=25 Bharat Ruparel (bruparel)
on 2008-11-11 04:59
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
44a43e7fef8e933e802a7802b4bd3525?d=identicon&s=25 John Small (johnsmall)
on 2008-11-11 10:15
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
This topic is locked and can not be replied to.