Jump to content

JOIN with COUNT


Souljacker

Recommended Posts

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?

 

 

Link to comment
Share on other sites

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       

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.