Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).

Furthermore I'd like to show a list of users voted, similar to fb (for now my solution is to get that list with new query fetched by ajax call.)

would it be better to create one query to get all results and read them separatelly from array or is it best practice to create separate requests?

My query bellow does get the ordered list but includes the empty or null rows and is very slow for table with >10000 results. any suggestions?

SELECT id, 
  title, 
  pollid, 
  COUNT(pollid) AS clean_count 
FROM 
(
  SELECT DISTINCT user_id, pollid 
  FROM tbl_votes
) AS tmp_tbl 
JOIN tbl_polls 
  ON tbl_polls.id = tmp_tbl.pollid 
WHERE not isnull(tmp_tbl.user_id) 
GROUP BY pollid 
ORDER BY clean_count DESC

Code to match results of votes with voters:

SELECT DISTINCT fbid, COALESCE(username, 'User not found.') 
AS username 
FROM tbl_votes JOIN tbl_users 
ON tbl_users.fbid = tbl_votes.user_id 
WHERE pollid = $band_id AND not isnull(username)
share|improve this question
    
Do you want to get rid of the empty/null rows for the result set? –  Angry Spartan Jan 25 '13 at 21:04
    
yes. or even better show them in separate field for comparison. –  joze novak Jan 25 '13 at 21:07
    
Modify your inner select? SELECT DISTINCT user_id, pollid FROM tbl_votes WHERE user_id IS NOT NULL –  Angry Spartan Jan 25 '13 at 21:08
    
Tried that allready. the result should be equal to the sum of the list of users voted. I added the code for list of voters. –  joze novak Jan 25 '13 at 21:13
add comment

1 Answer

up vote 0 down vote accepted

Try this:

SELECT id, 
  title, 
  pollid, 
  COUNT(pollid) AS clean_count,
  group_concat(DISTINCT u.name) AS user
FROM 
(
  SELECT DISTINCT user_id, pollid 
  FROM tbl_votes
) AS tmp_tbl 
JOIN tbl_polls 
  ON tbl_polls.id = tmp_tbl.pollid
JOIN tbl_users AS u 
ON tmp_tbl.user_id = u.user_id
WHERE not isnull(tmp_tbl.user_id) 
GROUP BY pollid 
ORDER BY clean_count DESC

This is the sample data that I used:

    CREATE TABLE tbl_polls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50)
  );


CREATE TABLE tbl_users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
  );

CREATE TABLE tbl_votes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pollid INT,
  user_id INT,
  INDEX (pollid,user_id),
  FOREIGN KEY (pollid) REFERENCES tbl_polls(id),
  FOREIGN KEY (user_id) REFERENCES tbl_users(user_id)
  );

INSERT INTO tbl_polls (title)
VALUES ('Title 1'),('Title 2'),('Title 3');


INSERT INTO tbl_users (name)
VALUES ('Bob'),('Jack'),('Joe'),('Tom');

INSERT INTO tbl_votes (pollid,user_id)
VALUES (1,2),(1,2),(1,2),(1,3),(2,4),(2,1),(2,4),(2,4),(3,1),(2,1),(1,4);

SQL FIDDLE DEMO

I have used group concat to get the names for your list. Furthermore, to speed up your database have you tried adding index to the foreign keys?

B.T.W. I don't understand your comment that some some votes lack pollid and user_id. If that is the case have a look at this.

share|improve this answer
    
Works perfectly! Thanks. Foreign keys are magic, though i had some problems implementing them since there are empty or null fields which answers your confusion with lacking fields i supose. Thanks again. –  joze novak Feb 1 '13 at 10:04
    
@Radical one more question, i simplified query by removing tbl_users as id is enough for now, my problem is group_concat limits set of result. how can i show all comma separated user_ids so that clean_count matches count of separated list? –  joze novak Feb 1 '13 at 13:45
    
@jozenovak I am a bit puzzled is this what you need sqlfiddle.com/#!2/5ce07/53. If not could you explain your question by showing the results you need? –  Mr. Radical Feb 1 '13 at 15:05
    
@Radical sory about the puzzles, here is the link sqlfiddle.com/#!2/98ae8/1/0. Now lets say Title 1 has > 1000 votes... in my case group_concat would show only first cca 30 user_ids because of the group_concat limits. Is there a way to show them all? –  joze novak Feb 1 '13 at 15:38
    
@jozenovak group_concat does indeed have a limit. You can alter the limit by using SET [GLOBAL | SESSION] group_concat_max_len = type in a value;. See SO stackoverflow.com/questions/5445048/…. However at some point it will also truncate your output. You could try to create a temporal table. I have tried both action before, so I will need to read up on that to answer your question. A less complicate option will probably be to use php to split up the query. –  Mr. Radical Feb 1 '13 at 15:49
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.