SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 08:31 AM
| ||||
| ||||
 Mysql Database groupwise select 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      |
|
02-28-2008, 08:31 AM
| |||
| |||
| Re: Mysql Database groupwise select 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 You wan't a match on user.id and account.mid with the highest value of account.id (even if the expiry date is not the most recent) right? If so you need a similar query to the one that I just posted in the Order By and Group By thread. |
|
02-28-2008, 08:31 AM
| |||
| |||
| Re: Mysql Database groupwise select 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 |
|
02-28-2008, 08:31 AM
| ||||
| ||||
| Re: Mysql Database groupwise select 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 |
Linear Mode
