JOIN with COUNT

[Souljacker]

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?

 

 

[Souljacker]

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       

[Barand]

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.)

[Souljacker]

How should I JOIN it?

[Souljacker]

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