[eluser]bapobap[/eluser]
Hi there,
I have three tables, users, votes, comments. The tables are fairly simple, for each vote or comment a user makes, an entry is made in the appropriate table. I'm trying to get a count for both of these for a user using this:
Code:
$business = $this->db
->select('u.username')
->select('COUNT(v.ident) AS votes', FALSE)
->select('COUNT(c.ident) AS comments', FALSE)
->join('votes v', 'v.ident = u.id AND v.active = 1', 'LEFT')
->join('comments c', 'c.ident = u.id AND c.active = 1', 'LEFT')
->where('u.id', 1)
->group_by('v.ident')
->group_by('c.ident')
->get('users u')
->row();
However I'm getting really weird counts back. If I comment out either one of the JOIN tables, I get the correct result but as soon as I bring in the other table JOIN I get a weird number. I'm not even sure what the number represents, sometimes it looks like it's adding something together and sometimes multiplying.
Is this just something you can't do in one query?