Hello folks! I've got a fun SQL problem for y'all.

Suppose I have these 2 tables:

Code:
poll_log:
   log_id    <- PK
   poll_id
   option_id
   user_id

poll_option:
   option_id   <- PK
   poll_id
   option_text


And here I filled in some random data I filled in to make it more clear:

Code:
poll_log:
log_id (PK) | poll_id | option_id | user_id
------------|---------|-----------|--------
1           | 2       | 5         | 1
2           | 2       | 6         | 1


poll_option:
option_id (PK) | poll_id | option_text
---------------|---------|------------
1              | 1       | A
2              | 1       | B
3              | 1       | C
4              | 2       | D
5              | 2       | E
6              | 2       | F


Now, my goal is to get all options one user voted in a poll separated by anything you like (I use ||). The problem is that I don't need the text of those votes (options), but the index of the option of a poll.

I have this query which select the option texts:

Code:
   SELECT l.poll_id, l.user_id,
   GROUP_CONCAT(o.option_text SEPARATOR '||') as votes
   FROM x_v2p5_xoopspoll_log l
   JOIN x_v2p5_xoopspoll_option o
   ON l.option_id = o.option_id
   AND l.poll_id = o.poll_id
   GROUP BY l.poll_id, l.user_id

which returns

Code:
   poll_id | user_id | votes
   --------|---------|------
   2       | 1       | E||F


However, as you see, that returns E||F, but I want to have this:

Code:
   poll_id | user_id | votes
   --------|---------|------
   2       | 1       | 1||2     <-- 1 and 2 because E is the second option of poll 2 and F is the third option of poll 3


Anyone a clue what I can do?

EDIT: I got the solution! Very Happy
Here's the query for anyone who likes it:


Code:
SELECT poll_id, user_id, time, GROUP_CONCAT(vote_index) as votes
FROM (
   SELECT l.poll_id, l.user_id, l.time, FIND_IN_SET(l.option_id, (
      SELECT GROUP_CONCAT(o.option_id) FROM poll_option o WHERE o.poll_id = l.poll_id
   )) - 1 AS vote_index
   FROM poll_log l) t
GROUP BY poll_id, user_id
I'd question the utility of the outputs you're getting- it seems like you'd either want to return the primary key of options (for referencing elsewhere in the system) or the option text (for presentation). When you start using indices like that it seems like you're going to make questionable assumptions in presentation to turn them back into labels.

If your database engine supports arrays that would probably also be preferable; maybe that relates to why you want integer offsets too?

Code:
SELECT user_id,
       poll_id,
       array_agg(option_id) AS option_ids,
       array_agg(option_text) AS option_labels
FROM poll_log JOIN poll_option
     ON poll_log.option_id = poll_option.option_id
GROUP BY user_id, poll_id
Tari wrote:
I'd question the utility of the outputs you're getting- it seems like you'd either want to return the primary key of options (for referencing elsewhere in the system) or the option text (for presentation). When you start using indices like that it seems like you're going to make questionable assumptions in presentation to turn them back into labels.

Fair questions, but the problem is that I'm migrating data from an old format to a new one. This query basically gets all the data and presents them in a new format I'm using. I don't really like this old format, but well, I didn't design it Razz.

Tari wrote:
If your database engine supports arrays that would probably also be preferable; maybe that relates to why you want integer offsets too?

Code:
SELECT user_id,
       poll_id,
       array_agg(option_id) AS option_ids,
       array_agg(option_text) AS option_labels
FROM poll_log JOIN poll_option
     ON poll_log.option_id = poll_option.option_id
GROUP BY user_id, poll_id

I'm using MySQL, so unfortunately that doesn't work.
PT_ wrote:
EDIT: I got the solution! Very Happy
Here's the query for anyone who likes it:


Code:
SELECT poll_id, user_id, time, GROUP_CONCAT(vote_index) as votes
FROM (
   SELECT l.poll_id, l.user_id, l.time, FIND_IN_SET(l.option_id, (
      SELECT GROUP_CONCAT(o.option_id) FROM poll_option o WHERE o.poll_id = l.poll_id
   )) - 1 AS vote_index
   FROM poll_log l) t
GROUP BY poll_id, user_id

I was in the process of whipping up a case statement, but that's a lot cleaner, good job!
  
Register to Join the Conversation
Have your own thoughts to add to this or any other topic? Want to ask a question, offer a suggestion, share your own programs and projects, upload a file to the file archives, get help with calculator and computer programming, or simply chat with like-minded coders and tech and calculator enthusiasts via the site-wide AJAX SAX widget? Registration for a free Cemetech account only takes a minute.

» Go to Registration page
Page 1 of 1
» All times are UTC - 5 Hours
 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

 

Advertisement