CODEWITHSUNDEEP
pythonmysqlsqlleft-join
L-R
L-R

Reputation: 1232

mysql left join : data from left table duplicated for every match

I have a simple structure like so:

Challenges table

id | title
-----------------
5  | My challenge woo

Goals table

id  | title         | challenge_id
-------------------------------------
35  | My goal here  | 5
38  | My goal here  | 5
39  | My goal here  | 5
40  | My goal here  | 5

Every challenge has multiple goals. If I try something like so:

SELECT c.id, c.title, g.id, g.title FROM challenges%s as c LEFT JOIN goals%s as g ON c.id=g.challenge_id WHERE c.id=%s

I get a copy of the challenge for every single goal that matche, which is tons of data I don't want every time. For example, this is the result : 

({'g.id': 35L, 'g.title': u'My goal here', 'id': 5L, 'title': u'My challenge woo'}, {'g.id': 38L, 'g.title': u'My goal here', 'id': 5L, 'title': u'My challenge woo'}, {'g.id': 39L, 'g.title': u'My goal here', 'id': 5L, 'title': u'My challenge woo'}, {'g.id': 40L, 'g.title': u'Another goal', 'id': 5L, 'title': u'My challenge woo'})

Notice how each instance of the challenge is part of the dictionary? I know mysql doesn't have any arrays, but I've tried CONCAT without any luck, as well as other similar queries such as these.

FYI I'm using mysqldb /w python

Upvotes: 3

Views: 691

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You need to do an aggregation after the join, with group_concat():

SELECT c.id, c.title, group_concat(ws_concat(':', g.id, g.title), separator ', ') as goals
FROM challenges c LEFT JOIN
     goals g
     ON c.id = g.challenge_id
WHERE c.id=%s
GROUP BY c.id, c.title;

This will create a list of goals on each line, with the form "id:title".

Upvotes: 2

Related Questions