I'm confused as to why you need the subselect at all? As it's all the same table why can't you just use
select candidate,count(*) as total from vote where voter <> '$me' group by candidate order by total desc; On Wed, Feb 27, 2008 at 9:04 AM, Olav Mørkrid <[EMAIL PROTECTED]> wrote: > hello > > i have a table "vote" which has the columns "voter" and "candidate". i > would like to make a list of the most popular candidates *except* > those who are on my favorite list. using a sub-select, it's easy: > > my favorites: > select candidate from vote where voter = '$me'; > > most popular: > select candidate from vote group by candidate order by count(*) desc; > > sub-select: > select candidate from vote where candidate not in (select candidate > from vote where voter = '$me') group by candidate order by count(*) > desc; > > however, sub-selects are very slow, so i need to find a speedy way. > i'm familiar with joins, but don't know how to use it for this case > where grouping is involved. > > please get in touch if you know how to solve it. > > thanks! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
