Representing left outer join in MySQL and Rails

Rails
1

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

2

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