mick wrote:
> Hi,
>
> I have a query problem that i hope somebody can help me solve.
>
> i have 2 tables
>
> user:
> id | name | ...
> 1 | mick | ...
> 2 | mat | ...
>
> account:
> id | mid | expiry |
> 1 | 1 | 20-05-2006 |
> 2 | 1 | 10-03-2004 |
> 3 | 1 | 06-07-2005 |
> 4 | 2 | 20-05-2005 |
>
>
> it may be considered a group-wise join but how can i get the resulting
> rows of all members to return:
>
> user.id | user.name | account.id | expiry
> 1 | mick | 3 | 06-07-2005
> 2 | mat | 4 | 20-05-2005
> and so on

Since I'm in a good mood, here it is:

SELECT `user`. * , `a1`. *
FROM `user`
LEFT JOIN `account` `a1` ON `user`.`id` = `a1`.`mid`
LEFT JOIN `account` `a2` ON `a1`.`mid` = `a2`.`mid` AND `a1`.`id` <
`a2`.`id`
WHERE `a2`.`id` IS NULL