mysql fail

May 7, 2010 10:05 AM

Either I don't know as much MySql as I thought, or I'm trying to do the impossible. Which is it...?

Okay. I have three tables.

  possibleAnswers (pid, text,...)         // some possible answers
  response (rid, pid, uid, timestamp,...) // a record of what user has chosen what
  users (uid, groupid,...)                // a list of users.

Now, I've selected a list of all possible answers, with a count of how many times they've been selected with the following...

  SELECT p.pid, r.rid, COUNT(*) as c
     FROM possibleAnswers  as p
        LEFT JOIN response as r ON p.pid=r.pid
     GROUP BY p.pid
     ORDER BY p.pid ASC

It's not perfect, since c=1 when there were no entries in the response table, but I can correct that in the PHP because rid has a null value if there weren't actually any responses.

Here's my problem...

I now need to extend this so that it only counts the responses from users who are in a certain group. My first attempt was this:

  SELECT p.pid, r.rid, COUNT(*) as c
     FROM possibleAnswers  as p
        LEFT JOIN response as r ON p.pid=r.pid
        LEFT JOIN user as u ON r.uid=u.uid
     WHERE u.groupid=123
     GROUP BY p.pid
     ORDER BY p.pid ASC

But (obviously, in retrospect) this returns nothing at all for possibleAnswers where there is no user who has selected it.

Is it possible to do what I'm trying to do...?

posted by twine42 to Computers & Internet (11 answers total)

I can't test this right now so can't give you the syntax, but you're going to need an outer join in order to see all from response whether or not there is a record in user. You may also need an outer join (+) in Oracle parlance (MySQL not sure, sorry) on the WHERE clause on user.
posted by jontyjago at 10:28 AM on May 7, 2010

Yes its possible. What you really want to do is called a "subselect".
You are asking give the database to SELECT the records from possible Answers WHERE the users are SELECTed WHERE those records are on a certain list of users..

Sorry - no time to give a full answer, just time for a tip.
posted by Nanukthedog at 10:35 AM on May 7, 2010

You might want to try putting the group restriction in the join clause. I.E., "LEFT JOIN user as u ON r.uid = u.uid AND u.groupid = 123". This should allow null columns to be generated when nobody picked a particular answer, but I'm not entirely sure.

Honestly though, if it were up to me I would just do regular joins instead of left joins and get all possibleAnswers in a separate pass. That seems a lot cleaner to me.
posted by kmz at 10:36 AM on May 7, 2010

Best answer: I don't know much about MySQL specifically, but with other SQL databases I'd probably use a subquery in place of the count(*) statement, so you get each possible answer exactly once, plus a count (which could be zero) for each one. Something like this:

  SELECT 
    p.pid,
    (select COUNT(*)
        FROM response as r 
        JOIN user as u ON r.uid=u.uid
        WHERE  p.pid=r.pid 
           and u.groupid=123) as c
    FROM possibleAnswers  as p
    GROUP BY p.pid
    ORDER BY p.pid ASC

posted by FishBike at 10:46 AM on May 7, 2010 [1 favorite]

OK, had a play with MySQL Nanukthedog has it - Subselect is the way - if it's one group you need to filter by you can use:

SELECT p.pid, r.rid, count( * )
FROM `possible` p, response r, user u
WHERE r.pid = p.pid
AND r.uid = u.uid
AND u.groupid = (
SELECT DISTINCT groupid
FROM user
WHERE groupid =100 )
GROUP BY p.pid

If you need more than one group then you'll need to use IN for the subselect:

AND u.groupid IN (SELECT distinct groupid FROM user WHERE....)

Hope this helps...
posted by jontyjago at 10:47 AM on May 7, 2010

Oops, strike out the 'GROUP BY p.pid' which I forgot to remove when I copied your original query. You don't need that line, though it doesn't hurt.
posted by FishBike at 10:49 AM on May 7, 2010

jontyjago: Why are you doing a subselect for groupids when you already have the groupids? That makes no sense. If you want to do subselects, you'd need u.uid in (select uid from user where groupid = 100).

But that doesn't solve the problem the OP is having, which is they want the possibleAnswers that nobody selected to also be displayed.
posted by kmz at 10:51 AM on May 7, 2010

Oh yeah, sorry. Ignore me. I'll go back to sleep...
posted by jontyjago at 11:02 AM on May 7, 2010

One of the nice things about the COUNT function is that you can give it a column name instead of "*" and it will return the number of rows that have a non-NULL value in that column. With that in mind, you can do this (I made sure the following query worked):

SELECT possibleAnswers.pid, COUNT(response.rid)
FROM possibleAnswers
LEFT JOIN (response JOIN users ON response.uid = users.uid AND users.groupid = 2)
    ON possibleAnswers.pid = response.pid
GROUP BY possibleAnswers.pid;

I feel a little dirty putting the groupid condition in the join, but you can replace it with a subquery if you want.
posted by pmdboi at 11:11 AM on May 7, 2010

Response by poster: Guys, my thanks to all of you.

I've marked FishBike's answer as 'best' because it's the one I tried out and made work - after I'd undone my attempts to simplify it and obscure it a little (just incase the boss is here).

For all I know, they all work. Most importantly, my page works.

Here, have a cookie.
posted by twine42 at 3:52 PM on May 7, 2010

Response by poster: And, as if to prove my apparent ineptitude tonight, I've managed to leave a <insert></insert> in the page, where I attempted to make a suitably cringeworthy 'joke'.

Balls.

<insert bad "best before 2038" joke here />
posted by twine42 at 3:56 PM on May 7, 2010