CODEWITHSUNDEEP
phpmysqljoinleft-joininner-join
Igor
Igor

Reputation: 673

LEFT JOIN without LIMIT existing in query

I've the following tables:

poll:

id | question   | votes
1  | Your name? | 2

poll_answers:

id | id_poll | answer
1  | 1       | John
2  | 1       | Jorge
3  | 1       | Charlie

And I want to do a SELECT to return the poll data and the poll_answers data with the id_poll corresponding to id on poll table.

So, I did:

SELECT question, votes, answer
FROM poll
LEFT JOIN poll_answers
ON poll.id = id_poll
WHERE poll.id = '1'
LIMIT 1

And the expected output would be return the poll data and the three answers on poll_answers table, but, the output is:

array(1) { [0]=> array(3) { ["question"]=> string(10) "Your name?" ["votes"]=> string(1) "2" ["answer"]=> string(5) "Charlie" } }

What I want:

array(1) { [0]=> array(3) { ["question"]=> string(10) "Your name?" ["votes"]=> string(1) "2" ["answer"]=> string(7) "Charlie" ["answer"]=> string(5) "Jorge" ["answer"]=> string(4) "John" } }

How to do this, using LIMIT on the main query but without LIMIT on the LEFT JOIN?

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I think you want aggregation:

SELECT p.question, p.votes, GROUP_CONCAT(pa.answer) as answer
FROM poll p LEFT JOIN
     poll_answers pa
     ON p.id = pa.id_poll
WHERE p.id = 1
GROUP BY p.id;

Upvotes: 1

Related Questions