[SOLVED] MySQL usage: Combining 2 SELECTS

[jasonfrog]

There is a list of candidates and a list of voters. Each voter is allowed to vote upto 5 times. The first vote allocates 5 points, the second vote allocated 4 points, the third 3 points, the fouth 2 point and fifth 1 point.

 

Each time some submits a vote an entry get made in the following table called nominations.

 

tablename: nominations
cid     int(20) 	
vid	  int(20)
points  tinyint(4)

 

e.g.

cid 	vid 	points
794 	693 	3
581 	693 	4
850 	693 	5
581 	514 	5
850 	514 	4
584 	514 	3
794 	514 	2
1981 	514 	1
850 	1164 	5
581 	1164 	4
584 	1164 	3
581 	573 	5
584 	573 	4
794 	573 	3

 

I would like to construct a query that lists the total points for each candidate (cid), but to only include points that have been allocated by a voter (vid) that has voted 3 (or more) times.

 

 

The following will produce a list of voters and the amount of times they have voted.

 

SELECT vid, count(*) as v FROM nominations WHERE 1 GROUP BY vid HAVING v > 2

 

 

The following will produce a list of candidate ids and the total amount of points they have received.

 

SELECT cid, SUM(points) AS p FROM nominations WHERE GROUP BY cid ORDER BY p DESC

 

 

How can I combine these two queries so that the result is a list of the total points for each candidate but only including points given by voters that have voted 3 (or more) times.

[nitin]

select cid, sum(points) from nominations n1 where 3<=(select count(*) from nominations n2 where n2.cid=n1.vid )  group by cid

 

is above query useful?

[jasonfrog]

select cid, sum(points) from nominations n1 where 3<=(select count(*) from nominations n2 where n2.cid=n1.vid )  group by cid

 

is above query useful?

 

many thanks. i had to change 'n2.cid=n1.vid' to 'n2.vid=n1.vid', but that works. huge thanks =)