Need help with SQL grouping tables - Cemetech | Forum

» Forum > Computer & Mobile

»
» View previous topic :: View next topic

PT_
3
Site Admin (Posts: 2089)

Need help with SQL grouping tables
27 Dec 2019 04:43:30 pm

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

Tari
3
Systems Wrangler (Posts: 3566)

27 Dec 2019 05:41:13 pm

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

PT_
3
Site Admin (Posts: 2089)

27 Dec 2019 05:54:08 pm

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.

mr womp womp
Official Cemetech Cat Manager (Posts: 1809)

Re: Need help with SQL grouping tables
27 Dec 2019 08:52:06 pm

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!

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

Reply to topic

»
» View previous topic :: View next topic

Page 1 of 1

» All times are UTC - 5 Hours