StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql many-many join
text
Body
copied!<p>I have a table structure as followed:</p> <p>I cannot post images yet, so an asssignment has many groups and many contacts, with a contact having a city and a city having a country.</p> <p>I want to count countries in assignmentsassignments per group</p> <p>And have following query</p> <pre><code>SELECT `parentCountry`.`id` as label ,count(`t`.`id`) as result, `parentGroup`.`description` as gr FROM `assignment` `t` LEFT OUTER JOIN `assignment_contact` `assignmentContacts` ON (`assignmentContacts`.`assignment`=`t`.`id`) LEFT OUTER JOIN `contact` `relatedContact` ON (`assignmentContacts`.`contact`=`relatedContact`.`id`) LEFT OUTER JOIN `locator_city` `location` ON (`relatedContact`.`city`=`location`.`id`) LEFT OUTER JOIN `locator_country` `parentCountry` ON (`location`.`country`=`parentCountry`.`id`) LEFT OUTER JOIN `assignment_group` `activeGroup` ON (`activeGroup`.`assignment`=`t`.`id`) AND (activeGroup.active=1) LEFT OUTER JOIN `group` `parentGroup` ON (`activeGroup`.`group`=`parentGroup`.`id`) GROUP BY gr,label ORDER BY gr </code></pre> <p>Running this query gets me results like</p> <pre><code>Group1 ,Belgium, 6 Group1 ,Nederlands,3 Group2, Belgium, 1 Group2, UK,6 </code></pre> <p>I want to get result</p> <pre><code>Group1 ,Belgium, 6 Group1 ,Nederlands,3 Group1 ,UK, 0 Group2, Belgium, 1 Group2, UK,6 Group2, Nederlands,0 </code></pre> <p>Please help, I have spend quite some hours, fiddling around with joins, to conclude that I have no 'theoretical' way of approaching this problem.</p> <p>The use of UNIONS (i.e. FULL OUTER JOINS) is forbidden, but I really don't think this should be needed.</p> <p>Edit: I have a horrible solution which can be </p> <p>Please help</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload