Souljacker Posted March 18, 2008 Share Posted March 18, 2008 Greetings, I'm losing it with this problem, maybe you guys can help me. I have two tables, IMAGES and VOTES. I want to make a query that returns the images and it's number of votes. I'm trying this: SELECT DISTINCT COUNT(gostou) as contador, poster_id, arquivo FROM voto, cartazes WHERE gostou = 1 GROUP BY poster_id ORDER BY contador DESC But it returns a strang count value When I make it without the JOIN SELECT DISTINCT COUNT(gostou) as contador, poster_id FROM voto WHERE gostou = 1 GROUP BY poster_id ORDER BY contador DESC It returns 5 for the count of the frist image, wich is right. With the first query it returns 200 oO Any idea? Quote Link to comment Share on other sites More sharing options...
Souljacker Posted March 18, 2008 Author Share Posted March 18, 2008 Oh, here are the tables: Field Type Collation Null Key Default Extra Privileges Comment -------- ------------ ----------------- ------ ------ ------- -------------- -------------------- ------- lang varchar(3) latin1_swedish_ci YES (NULL) select,insert,update arquivo varchar(100) latin1_swedish_ci YES (NULL) select,insert,update id int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update titulo varchar(50) latin1_swedish_ci YES (NULL) select,insert,update texto text latin1_swedish_ci YES (NULL) select,insert,update autor varchar(30) latin1_swedish_ci YES (NULL) select,insert,update email varchar(30) latin1_swedish_ci YES (NULL) select,insert,update data datetime (NULL) YES (NULL) select,insert,update aprovado int(1) (NULL) YES 0 select,insert,update galeria int(1) (NULL) YES 0 select,insert,update Field Type Collation Null Key Default Extra Privileges Comment --------- ----------- ----------------- ------ ------ ------- -------------- -------------------- ------- id int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update poster_id int(11) (NULL) NO PRI select,insert,update gostou int(1) (NULL) YES 0 select,insert,update ip varchar(20) latin1_swedish_ci YES (NULL) select,insert,update Quote Link to comment Share on other sites More sharing options...
Barand Posted March 18, 2008 Share Posted March 18, 2008 you haven't specified how the tables should be joined, so it joins every record in first table with every record in the second which probably explains the strange count. (If you have 100 recs in one table and 10 in the other you get 1000 rows.) Quote Link to comment Share on other sites More sharing options...
Souljacker Posted March 18, 2008 Author Share Posted March 18, 2008 How should I JOIN it? Quote Link to comment Share on other sites More sharing options...
Souljacker Posted March 18, 2008 Author Share Posted March 18, 2008 SELECT DISTINCT COUNT(gostou) as contador, poster_id, arquivo FROM voto, cartazes WHERE gostou = 1 AND cartazes.id = voto.poster_id GROUP BY poster_id ORDER BY contador DESC I've made it I forget the basics. .. the primary keys Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.