CODEWITHSUNDEEP
mysql
dlwiest
dlwiest

Reputation: 655

GROUP_CONCAT on multiple tables in one query

I'm working on a polling site, wherein the basic information for each poll is stored in one table, the choices are stored in another, and the votes are stored in yet another. This structure gives me the most flexibility but, because I'm not a mySQL expert, I'm having some trouble with the query to retrieve all the relevant data at once.

So far, I've been able to use GROUP_CONCAT to select the choices as a CSV, but with the current structure, I'm not sure how to use GROUP_CONCAT again to also grab a list of votes. The query I'm using so far looks like this: 

SELECT polls.id AS id, polls.name AS name, polls.time AS time, polls.description AS description, ch.choices 
FROM (SELECT poll, GROUP_CONCAT(name) AS choices FROM choices GROUP BY poll) AS ch 
JOIN polls ON ch.poll = polls.id 
ORDER BY polls.time DESC 
LIMIT 0, ' . ($count - 1)

Relevant columns in the votes table are 'poll', and 'vote', where poll is the same as polls.id and vote corresponds with choice.id. Specifics aside though, all I really need to know is how to use GROUP_CONCAT to retrieve values from two tables, instead of just one, in a single query. I would appreciate any input. Thanks!

Upvotes: 1

Views: 5875

Answers (2)

dlwiest
dlwiest

Reputation: 655

The solution ended up being much simpler than I realized; my original query was unnecessarily complicated. I ended up performing the GROUP_CONCATs as function in the SELECT statement, and that worked fine. Here's what I ended up with:

SELECT polls.id AS id, polls.name AS name, polls.time AS time, polls.description AS description, 
(SELECT GROUP_CONCAT(name SEPARATOR "|") FROM choices WHERE poll = polls.id) as choices, 
(SELECT GROUP_CONCAT(choice SEPARATOR "|") FROM votes WHERE poll = polls.id) as votes 
FROM polls 
ORDER BY polls.time DESC

Upvotes: 1

eabraham
eabraham

Reputation: 4164

Your requirements are somewhat unclear. If you document the table structures for poll, vote and choice it might be easier to help.

My first attempt:

Select polls.id as id, polls.name as name, polls.time as time,
    polls.description as description, ch.choices
FROM polls
INNER JOIN vote ON polls.id=vote.poll_id
INNER JOIN vote ON vote.id=choice.vote_id
ORDER BY polls.time DESC
LIMIT 0, 30

Upvotes: 0

Related Questions