# Representing left outer join in MySQL and Rails

**URL:** <https://www.ruby-forum.com/t/representing-left-outer-join-in-mysql-and-rails/153015>\
**Category:** Rails\
**Created:** [November 11, 2008, 4:59am UTC](https://www.ruby-forum.com/t/representing-left-outer-join-in-mysql-and-rails/153015 "2008-11-11T04:59:54Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [November 11, 2008, 4:59am UTC](https://www.ruby-forum.com/t/representing-left-outer-join-in-mysql-and-rails/153015/1 "2008-11-11T04:59:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [November 11, 2008, 10:15am UTC](https://www.ruby-forum.com/t/representing-left-outer-join-in-mysql-and-rails/153015/2 "2008-11-11T10:15:53Z")

</div>

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 S.

Bharat R. 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